About Question enthuware.oce-jpad.v6.2.576 :
Moderator: admin
-
- Posts: 3
- Joined: Sun Jan 03, 2016 6:10 pm
- Contact:
About Question enthuware.oce-jpad.v6.2.576 :
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.
-
- Site Admin
- Posts: 10228
- Joined: Fri Sep 10, 2010 9:26 pm
- Contact:
Re: About Question enthuware.oce-jpad.v6.2.576 :
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.
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.
-
- Posts: 3
- Joined: Sun Jan 03, 2016 6:10 pm
- Contact:
Re: About Question enthuware.oce-jpad.v6.2.576 :
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.
Answer 4 generates the sql using the HAVING clause which is applied after the aggregate function (and thus can filter it).
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:
It works and can be translated into a CriteriaQuery like this:
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
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
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
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
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();
}
Thx for leading me into the right direction
-
- Site Admin
- Posts: 10228
- Joined: Fri Sep 10, 2010 9:26 pm
- Contact:
Re: About Question enthuware.oce-jpad.v6.2.576 :
Great!
If you like our products and services, please help us by posting your review here.
Who is online
Users browsing this forum: No registered users and 1 guest