Struggling to convert a left join query in JPQL

I am struggling to convert this sql query

SELECT feature.*, rolefeature.roleId, rolefeature.permission 
    FROM feature 
        Left JOIN rolefeature 
            ON feature.featureId = roleFeature.featureId 
                and roleFeature.roleId = ?

which works fine.

to JPQL. I have this

select f, rf.role.roleId as roleId, rf.permission 
    from Feature f 
        LEFT JOIN f.roleFeatures rf 
            and rf.role.roleId = :roleId

but it seems to complain about the AND keyword. I'm at a bit of a loss. If I use a WHERE instead of an AND, it does not return the correct data

Solution

First of all, the and clause makes no sense here. You need a where clause.

Second, querying on rf.role implicitely makes an inner join between RoleFeature and Role. You would see it by inspecting the generated SQL. You need to do a second left join to access Role.

Third, if you put the restriction on the role ID in the where clause, the left join will become an inner join. So you need the equivalent of the on clause in JPQL. I don't think JPQL has that, but Hibernate supports it using the with keyword:

select f, role.roleId as roleId, rf.permission 
from Feature f 
left join f.roleFeatures rf 
left join rf.role role with role.roleId = :roleId

Maybe you can simulate it with

left join rf.role role
where role.roleId is null or role.roleId = :roleId