LEFT JOIN ON() in JPQL

Question

I have two entities:

• User: id:long, name:String
• Player: id:long, owner:User, points:int

Now I want to select a User and his associated Player in one JPQL query. In SQL I'd do it like this:

SELECT u.*, p.* FROM User u
LEFT JOIN Player p ON (p.owner_id = u.id)
WHERE u.name = ...

My first instinct was to do it like this in JPQL

SELECT u, p FROM User u LEFT JOIN Player p ON (p.owner = u) WHERE u.name = ...

But I don't think the ON clause is supported in JPQL. I do need it however, because User has no reference to Player (many things other than Player could be attached to a User). How can I solve this one?

Answer 1

You have a relationship from Player to User, so you can invert the join to follow it:

SELECT u, p FROM Player p RIGHT JOIN p.owner u WHERE ...