I have the following method in my Spring Boot app in my Repository:

@Query("""
            SELECT tw FROM TW tw
            INNER JOIN tw.docks d // TW entity has list of docks
            WHERE d.id = :dockId
            AND tw.status <> 'DELETED'
            """)
List<TW> findAllTWs(long dockId);

How to check if TW has exactly one dock (tw.docks.size() == 1)? I have to filter out tw.docks with more than 1 dock (I want only list of TWs with one dock) Probably I need native query for that

CodePudding user response：

Try with SIZE function which is valid in jpql language.

@Query("""
            SELECT tw FROM TW tw
            INNER JOIN tw.docks d // TW entity has list of docks
            WHERE d.id = :dockId
            AND tw.status <> 'DELETED' AND SIZE(tw.docks) = 1
            """)

CodePudding user response：

This should generate sql with a subquery:

SELECT tw 
FROM TW tw 
WHERE tw.docks.size = 1

See the appended documentation, size is a special HQL property.