JPQL query with left join searching for entity

Question

Based on following article JPQL, OR only returning result of one condition? I created the following query

Collection<Profile> profiles = dataManager.load(Profile.class).view("profile-view")
        .query("select p from userlifecyclemgmt_Profile p " +
                " left join p.legalEntities f where" + 
                " p.isAvailableForAll = true or :legalEntity MEMBER OF f" ) 
        .parameter("legalEntity", serviceRequest.getHrUser().getLegalEntity())
        .list();

The parameter :legalEntity is an object of class LegalEntity. This query gives me the following error:

JpqlSyntaxException: Errors found for input jpql:[select p from userlifecyclemgmt_Profile p  left join p.legalEntities f where p.isAvailableForAll = true or :legalEntity MEMBER OF f]
CommonErrorNode [<unexpected: [@35,120:125='MEMBER',<113>,1:120], resync=:legalEntity MEMBER OF f>]

Using the same query slightly modified:

Collection<Profile> profiles = dataManager.load(Profile.class).view("profile-view")
        .query("select p from userlifecyclemgmt_Profile p " +
                " left join p.legalEntities f where" +
                " p.isAvailableForAll = true or :legalEntity MEMBER OF p.legalEntities" )
        .parameter("legalEntity", serviceRequest.getHrUser().getLegalEntity())
        .list();

has no error, but gives me only the results of the second part of the OR statement. I do understand that JPQL creates an INNER JOIN query in the background and that that's the reason. However, how do I get the first query to work?

Thanks in advance!

I implemented a workaround using 2 queries and then merging the results. But that's not really an acceptable solution. — Edwin Krause

alex valuiskyi alex valuiskyi · Accepted Answer · 2020-09-02T12:39:36

You should modify the query this way

select distinct p 
from userlifecyclemgmt_Profile p left join p.legalEntities f 
where p.isAvailableForAll=true or f=:legalEntity

from userlifecyclemgmt_Profile p left join p.legalEntities f expression generates left join native query which returns plain result profile-legalEntity. f become alias for single legalEntity, can't be used in member of, otherwise p.legalEntities can.

select p returns the same profile multiple (legalEntities.size()) times. So you need distinct.

JPQL query with left join searching for entity

1 Answers