java - JPQL Eclipselink - 如何连接两个表并显示另一个表中没有项目的项目

Question

我尝试选择所有根本没有出价的订单，但出现异常，我该怎么做？

这就是我想做的:

SELECT o, COUNT(b) FROM OrderEntity o LEFT JOIN OrderBidEntity b ON o.id = b.orderId WHERE COUNT(b) = 0 AND o.created < :date ORDER BY o.created ASC

Exception in thread "Thread-4" java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: 
Exception Description: Syntax error parsing [SELECT o, COUNT(b) FROM OrderEntity o LEFT JOIN OrderBidEntity b ON o.id = b.orderId WHERE COUNT(b) = 0 AND o.created < :date ORDER BY o.created ASC]. 
[91, 99] The COUNT function is only allowed in the SELECT, GROUP BY, ORDER BY and HAVING clauses.
    at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1605)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:342)
    at com.sun.proxy.$Proxy38.createQuery(Unknown Source)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAcce

Answer 1

我目前无法仔细检查这一点，但错误消息非常具有描述性。您可以尝试调整您的查询，如下所示:

SELECT o FROM OrderEntity o WHERE (SELECT COUNT(b) FROM OrderBidEntity b WHERE o.id = b.orderId) = 0 AND o.created < :date ORDER BY o.created ASC