JPA select new with aggregate function on member collection
I have a query like the following:
Select new mypackage.MyClass( u, max(sc.serviceDate))
from Unit u left join u.serviceCalls sc
where u.organization.key 开发者_如何学JAVA= :organizationKey
So, my mapping is that I have a Unit, which has a collection of ServiceCalls (FetchType.LAZY), and also has an organization. Each ServiceCall has a serviceDate.
In my query, I would like to select the entire Unit, but not all of the serviceCalls. I would like to fetch the most recent serviceDate if one exists.
Attempting to execute the query through eclipselink on postgres gets me the following (I removed some selected fields from the query output)
Internal Exception: org.postgresql.util.PSQLException: ERROR: column "t0.key" must appear in the GROUP BY clause or be used in an aggregate function
Position: 8
Error Code: 0
Call: SELECT t0.KEY, MAX(t1.service_date) FROM unit t0 LEFT OUTER JOIN service_call t1 ON (t1.unit_key = t0.KEY), organization t2 WHERE ((t2.KEY = ?) AND (t2.KEY = t0.organization_key))
It looks like max is being applied across all service calls instead of getting me the max for each unit. Is there a way to do this or am I going to have to just getch all the service calls and get the max that way?
Do you need a group by u
at the end?
精彩评论