Page 1 of 1

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

Posted: Wed May 30, 2012 3:33 pm
by cosminvacaroiu
If for B we would have used size instead of count, wouldn't have been true ?

Code: Select all

cb.size(student.get(Student_.presentations)

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

Posted: Thu May 31, 2012 6:56 pm
by admin
Yes, that is a good point. It would have been valid in that case.

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

Posted: Mon Sep 21, 2015 6:06 pm
by romsky
The SIZE() is not a aggregate function. It will not work with ORDER BY clause.

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

Posted: Sat Dec 24, 2016 1:47 pm
by johnlong
I did not quite understand why do we need to get departments with number of presentations 0
You need a left outer join between Student and Presentation because you want to include even those departments that have no presentation. Their count should be 0. If you use INNER join (which is the default when you don't specify a JoinType), you will only those depts which have a presentation.
What is the use of zero presentation? First option will provide same result, without zero presentations, correct?

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

Posted: Sun Dec 25, 2016 1:02 pm
by admin
Because you want to find out the number of presentations done by each Dept. What if a dept has made no presentation? You do want to see that as zero in your result, right?

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

Posted: Sun Dec 25, 2016 6:26 pm
by johnlong
Yes, maybe, depends on logic if 0 is still considered as number.
Thank you.

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

Posted: Thu Sep 13, 2018 9:39 pm
by __JJ__
2. group by s.dept implies:
q.groupBy(student.get(Student_.enrollment).get(Enrollment_.dept));

Note that customer.join("enrollment") is same as customer.join(Student_.enrollment).

3. Since we are selecting individual fields (dept and count) as opposed to a complete entity object, we need to select a tuple:
q.select(cb.tuple(student.get("enrollment").get("dept"), cb.count(student) )), which is same as
q.multiselect(student.get(Student_.enrollment).get(Enrollment_.dept), cb.count(presentations) )
Hi,
In point 3, why/how is

Code: Select all

count(student)
replaced by/functionally equivalent to

Code: Select all

count(presentations)
?
In point 2, is that a typo - do you mean
Note that student.join("enrollment") is same as student.join(Student_.enrollment)?

Thanks.

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

Posted: Fri Sep 14, 2018 10:21 pm
by admin
1. Yes, in point 2, it should be student.join and not customer.join. Fixed. Thank you for your feedback!
2. I don't see count(student) written anywhere in the explanation. I see count(presentation).

HTH,
Paul.

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

Posted: Sun Sep 16, 2018 9:22 pm
by __JJ__
admin wrote:
Fri Sep 14, 2018 10:21 pm
1. Yes, in point 2, it should be student.join and not customer.join. Fixed. Thank you for your feedback!
2. I don't see count(student) written anywhere in the explanation. I see count(presentation).

HTH,
Paul.
Hi

In your explanation you changed the second argument from cb.count(student) to cb.count(presentations), but said the two queries are equivalent.
Please see the attached image:

Image

The reason I asked was because a) I wasn't sure if it was a typo or not (I think it's just a typo), and b) regardless of that whether it changes the result. I think the answers to both are yes.

First, the question asks for a query that returns the number of presentations per department; so returning the number of students per department is probably not the right thing.
Second, I just tested it, and found (as I guessed) that the SQL generated is different when you change the argument to count. Of course, sometimes the argument to count makes no difference, but here the result is different.

Code: Select all

cq.multiselect(empRoot.get(Employee_.enrollment).get(Enrollment_.department), cb.count(empRoot));
==> generated SQL:
select e.department, count(e.id) 
from Employee e left outer join Presentation p 
on e.id=p.presenter_id 
group by e.department       
==> results        
department=MATHS, count=3
department=ENGLISH, count=1


vs

Code: Select all

cq.multiselect(empRoot.get(Employee_.enrollment).get(Enrollment_.department), cb.count(presentations));
==> generated SQL:
select e.department, count(p.presentationTitle) 
from Employee e left outer join Presentation p 
on e.id=p.presenter_id 
group by e.department
==> results
department=MATHS, count=3
department=ENGLISH, count=0
Apologies for the longwindedness of all this. I just wanted to be sure that I wasn't missing something. With all my focus on Java my SQL is getting a little rusty from neglect.
Thanks

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

Posted: Sun Sep 16, 2018 10:34 pm
by admin
The statement in the explanation is only talking about the equivalency of Employee._enrollment and "enrollment". This is what I fixed after your first post.
test.png
test.png (43.13 KiB) Viewed 13341 times

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

Posted: Tue Sep 18, 2018 11:26 pm
by __JJ__
Alright, cheers.

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

Posted: Sun Sep 23, 2018 12:37 pm
by henri.davids
cb.count(student.get(Student_.presentations) will not work until you join the Student table with Presentation table.
Except for the fact that you need an outer join, why is option 2 wrong? I thought that a path expression (get) can create an implicit join?