JPQL multiple left join with one class

Question

I have following problem with JPQL: One Class has three fields:

public class Message{
  Integer ownerId;
  Integer fromUserId;
  Integer toUserId;
  //rest of implementation
}

then I want to search in the database by the user names, but we have 3 users attached to this Message: Owner, Recipient and Sender, so what I did in SQL is:

SELECT * FROM message m 
LEFT JOIN user o ON m.owner_id=o.id 
LEFT JOIN user fu ON m.from_user_id = fu.id
LEFT JOIN user tu ON m.to_user_id = tu.id
WHERE o.name like '%foo%' OR fu.name like '%foo%' OR tu.name like '%foo%';

Which is maybe not the fastest query, but works fine. Then I wanted to translate it to JPQL, but it's not easy. I get exception on 'on' token, I understand that JOIN is possible if the class has relation defined to another class, but here the relation is a bit complicated as we have 3 users related to one Message. Any ideas how to solve this?

score 7 · Accepted Answer · answered Sep 19 '12 at 21:03

7

I think this should do the trick:

FROM message m                -- SELECT * is optional in JPQL
LEFT JOIN m.owner o           -- In JPQL, you put the path to the related object
LEFT JOIN m.from_user_id fu   -- and JPA does the rest using the mappings
LEFT JOIN m.to_user_id tu
WHERE o.name like '%foo%' OR fu.name like '%foo%' OR tu.name like '%foo%';

The thing to remember is that in JPQL the JOIN conditions are defined in the mappings, so you don't specify them in the queries: just put the path to the object you want to reference and JPA will infer the rest from the mappings.

answered Sep 19 '12 at 21:03

gpeche

21,974
5
38
51

WEll, it's a good answer so I give +, however in my example it wont work as we don't have mappings defined, we just store integers without relation defined.. But thanks! – Mat Sep 20 '12 at 08:05
By the way, as long as I was reading this is impossible to do wthout correct mappings, so the question is not solvable – Mat Sep 20 '12 at 08:06
If you are working with JPA, mappings are not an option, you *need* them to do anything useful. – gpeche Sep 20 '12 at 08:09

score 2 · Answer 2 · answered Jan 23 '20 at 20:04

I'm using Spring Data JPA version 2.1.5 (feb 2019) and I have multiple left joins working as you can see in the following example:

@Query("select parentMenu from ObMenu parentMenu "
            + "left join ObTreeNode parentTreeNode on parentMenu.id=parentTreeNode.nodeId "
            + "left join ObTreeNode childTreeNode on parentTreeNode.nodeId=childTreeNode.parentId "
            + "where  childTreeNode.nodeId=:childMenuId")
    ObMenu findParentMenu(@Param("childMenuId") String childMenuId);

JPQL multiple left join with one class

2 Answers2

Linked