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

Moderator: admin

Post Reply
cosminvacaroiu

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

Post 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)

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

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

Post by admin »

Yes, that is a good point. It would have been valid in that case.
If you like our products and services, please help us by posting your review here.

romsky
Posts: 39
Joined: Thu Jan 29, 2015 4:49 pm
Contact:

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

Post by romsky »

The SIZE() is not a aggregate function. It will not work with ORDER BY clause.

johnlong
Posts: 197
Joined: Mon Jun 20, 2016 5:06 pm
Contact:

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

Post 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?

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

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

Post 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?
If you like our products and services, please help us by posting your review here.

johnlong
Posts: 197
Joined: Mon Jun 20, 2016 5:06 pm
Contact:

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

Post by johnlong »

Yes, maybe, depends on logic if 0 is still considered as number.
Thank you.

__JJ__
Posts: 125
Joined: Thu Jul 05, 2018 6:44 pm
Contact:

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

Post 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.

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

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

Post 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.
If you like our products and services, please help us by posting your review here.

__JJ__
Posts: 125
Joined: Thu Jul 05, 2018 6:44 pm
Contact:

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

Post 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

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

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

Post 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 13353 times
If you like our products and services, please help us by posting your review here.

__JJ__
Posts: 125
Joined: Thu Jul 05, 2018 6:44 pm
Contact:

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

Post by __JJ__ »

Alright, cheers.

henri.davids
Posts: 7
Joined: Tue Sep 04, 2018 9:51 am
Contact:

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

Post 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?

Post Reply

Who is online

Users browsing this forum: No registered users and 14 guests