JPA 在成员集合上使用聚合函数选择新的
我有一个如下所示的查询：
Select new mypackage.MyClass( u, max(sc.serviceDate))
from Unit u left join u.serviceCalls sc
where u.organization.key = :organizationKey
因此，我的映射是我有一个 Unit，它有一个 ServiceCalls (FetchType.LAZY) 的集合，并且还有一个组织。每个 ServiceCall 都有一个 serviceDate。
在我的查询中，我想选择整个单元，但不是所有服务调用。我想获取最新的 serviceDate（如果存在）。
尝试通过 postgres 上的 eclipselink 执行查询会得到以下结果（我从查询输出中删除了一些选定的字段）
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))
看起来 max 正在应用于所有服务调用，而不是为我获取每个单元的最大值。有没有办法做到这一点，或者我是否必须获取所有服务调用并以这种方式获得最大值？
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 = :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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
最后需要
group by u吗？Do you need a
group by uat the end?