JPQL一班多人左加入 [英] JPQL multiple left join with one class

问题描述

我的JPQL有以下问题: 一类包含三个字段:

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

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

然后我想通过用户名在数据库中进行搜索，但是此消息附加了3个用户:所有者，收件人和发件人，所以我在SQL中所做的是:

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%';

这也许不是最快的查询，但是效果很好.然后我想将其转换为JPQL，但这并不容易.我在'on'令牌上遇到异常，我知道如果该类具有与另一个类的关系，则JOIN是可能的，但是这里的关系有点复杂，因为我们有3个用户与一个Message相关.有什么想法可以解决这个问题吗?

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?

推荐答案

我认为这应该可以解决问题:

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%';

要记住的事情是，在JPQL中，JOIN条件是在映射中定义的，因此您不必在查询中指定它们:只需将路径放入您要引用的对象，JPA便会从映射中推断出其余条件.映射.

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.

这篇关于JPQL一班多人左加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！