带条件的Spring JPQL和SIZE函数

Question

我在Spring Boot应用程序中使用Hibernate和MySQL db的存储库中使用以下方法:

@Query("""
SELECT tw FROM TW tw
LEFT JOIN tw.docks d // where dock has flag isDeleted
WHERE d.id = :dockId
AND tw.status <> 'DELETED' AND SIZE(tw.docks) = 1
""")

我想给我的SIZE函数添加一些条件，使dock.isDeleted = false只计数docks。是可能的还是我必须写一些本地查询?

编辑:添加ON d.isDeleted = false当然不是解决办法，因为它不会影响SIZE的功能

TimeWindow表和Dock表连接到第三个表time_window_dock表

Answer 1

您可以使用聚合函数通过查询编写组:

SELECT tw 
FROM TW tw
LEFT JOIN tw.docks d ON d.isDeleted = false
WHERE d.id = :dockId
AND tw.status <> 'DELETED'
GROUP BY tw
HAVING COUNT(d.id) = 1