About Question enthuware.oce-jpad.v6.2.576 :

Moderator: admin

Post Reply
fargott
Posts: 3
Joined: Sun Jan 03, 2016 6:10 pm
Contact:

About Question enthuware.oce-jpad.v6.2.576 :

Post by fargott »

Could you explain why answer 2 should not be valid, too? I struggle to understand why, because no aggregate functions (like sum, avg,... ) are used by the groupBy(). Due to my understanding filtering the groupBy using having() is ok, but limiting the result using where() should also work.

admin
Site Admin
Posts: 10036
Joined: Fri Sep 10, 2010 9:26 pm
Contact:

Re: About Question enthuware.oce-jpad.v6.2.576 :

Post by admin »

Since you are doing a "group by" on student.dept, you have to limit using "having". Where won't work.
You may want to try running the code with where and see the sql that it generates.

Paul.
If you like our products and services, please help us by posting your review here.

fargott
Posts: 3
Joined: Sun Jan 03, 2016 6:10 pm
Contact:

Re: About Question enthuware.oce-jpad.v6.2.576 :

Post by fargott »

Hi Paul,
thanks for your quick reply. I did as you advised and think I finally got it. The WHERE clause is applied before any aggregate function (count is one of them) is evaluated. That's the reason. Answer 2 generates the following incorrect sql, in which inside the WHERE clause a count() is used - which is wrong.

Code: Select all

select student0_.dept as col_0_0_ from student student0_ left outer join student_presentations presentati1_ on student0_.id=presentati1_.student_id left outer join presentation presentati2_ on presentati1_.presentations_id=presentati2_.id where count(presentati2_.id)=0 group by student0_.dept 
Answer 4 generates the sql using the HAVING clause which is applied after the aggregate function (and thus can filter it).

Code: Select all

select student0_.dept as col_0_0_ from student student0_ left outer join student_presentations presentati1_ on student0_.id=presentati1_.student_id left outer join presentation presentati2_ on presentati1_.presentations_id=presentati2_.id group by student0_.dept having count(presentati2_.id)=0
However, I wanted to try to produce a valid statement using a where clause so I manually changed the SQL to use WHERE instead of HAVING, like that:

Code: Select all

select student0_.dept as col_0_0_ from student student0_ left outer join student_presentations presentati1_ on student0_.id=presentati1_.student_id left outer join presentation presentati2_ on presentati1_.presentations_id=presentati2_.id where presentati2_.id is null group by student0_.dept
It works and can be translated into a CriteriaQuery like this:

Code: Select all

	public List<Object> getDeptsWithNoPresentations(){
		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery q = cb.createQuery(); 
		Root<Student> student = q.from(Student.class); 
		Join<Student, Presentation> presentations = student.join("presentations", JoinType.LEFT); 
		q.groupBy(student.get("enrollment").get("dept")); 
		q.select(student.get("enrollment").get("dept")); 
		//q.where(cb.equal(cb.count(Student_.presentations), 0));
		q.where(cb.isNull(presentations)); 
		TypedQuery tq = em.createQuery(q);
		return tq.getResultList();
	}
The trick in this case is to just use cb.isNull in the where clause and not any aggregate function.

Thx for leading me into the right direction :D

admin
Site Admin
Posts: 10036
Joined: Fri Sep 10, 2010 9:26 pm
Contact:

Re: About Question enthuware.oce-jpad.v6.2.576 :

Post by admin »

Great! 8-)
If you like our products and services, please help us by posting your review here.

Post Reply

Who is online

Users browsing this forum: No registered users and 48 guests