Hibernate join fetch with conditions for the right side of the join

Question

I've got following named query written in HQL:

query = "select u from User u " +
   "join fetch u.devices d " +
   "join fetch u.ipRestrictions ir " +
   "join fetch u.employees e where " +
   "u.login = :login and " +
   "u.enumDataState.id = :dataStateId and " +
   "d.enumDataState.id = :dataStateId and " +
   "ir.enumDataState.id = :dataStateId and " +
   "e.enumDataState.id = :dataStateId ")

When I execute it I pass an email and dataStateId value. I can see in the database table, that there is a user with the given email and dataStateId, but I do not get any result.

The purpose of the query is to join fetch the active user (dataStateId tells if the user is active or not) with the given email with devices, ipRestrictions and employees which are also active.

I already saw some answers on stackoverflow, some suggested to use left join fetch but the left join fetch didn't work either.

I am sure, that there is a user with the given email and the given dataStateId, because I can see him in the database data editor.

How can I solve this here? It's a huge problem, because I will have a lot of such functions where I need to check the right side of the join fetch using some condition.

I am using Hibernate v5.2.

Answer 1

From your comment, you should use :

select u from User u
    left join fetch u.devices d 
    left join fetch u.ipRestrictions ir
    left join fetch u.employees e 
where 
    u.login = :login and u.enumDataState.id = :dataStateId and 
    (d is null or d.enumDataState.id = :dataStateId) and 
    (ir is null or ir.enumDataState.id = :dataStateId) and
    (e is null or e.enumDataState.id = :dataStateId)

Note: the is null may not work (depends on JPA version and implementation). You may need to use

• u.devices is null
• u.devices is empty