我如何写一个左外连接与子选择在jpql?

Question

我使用hibernate与Postgres数据库交互。我正在尝试进行的查询选择尚未分配给分支的产品。通过branch_product表，产品和分支之间存在多对多关系。我可以用本地SQL编写查询:

SELECT p.*
FROM product p
LEFT JOIN (
SELECT bp.product_id
FROM branch_product bp
WHERE bp.branch_id = ?
) AS sub ON sub.product_id = p.id
WHERE p.tenant_id = ?
AND sub.product_id IS NULL;

查询的工作方式是通过构造包含分支中所有产品的行的sub表。然后，没有与sub连接的产品中的任何行都是我想要的-尚未添加到分支的产品。

我还没有弄清楚如何使用jpql来表达这个查询。如果我使用本机查询，那么hibernate实体就不能轻松加载，因为没有设置列的json映射。如果我可以用jpql来做这个查询，那将节省很多工作。

Answer 1

所以在Hibernate 6中，当然可以这样写HQL:

select p.id, ph.n 
from Person p 
left join 
(select phone.number as n, phone.person as pp from Phone phone) as ph 
on ph.pp=p

，我相信这与你的查询非常相似。

但是我不明白这样做有什么意义。在我看来，你的查询可以很容易地重写为不使用子查询。

在我的例子中，我将写:

select p.id, ph.number
from Person p 
left join Phone ph on ph.person=p

和在你的SQL查询我会写:

SELECT p.*
FROM product p
LEFT JOIN branch_product bp
ON bp.product_id = p.id and bp.branch_id = ?
WHERE p.tenant_id = ?
AND bp.product_id IS NULL

也许我错过了什么，我不明白你的子查询的目的是什么。

Answer 2

我最终弄清楚了如何编写我需要的jqpl:

SELECT p
FROM Product p
LEFT JOIN ( 
SELECT bp.product.id as productId
FROM BranchProduct bp
WHERE bp.branch.id = :branchId
) AS sub ON sub.productId = p.id
WHERE sub.productId IS NULL

我想是内部选择的语法把我绊倒了。它对SELECT bp FROM BranchProduct bp不起作用。我必须从bp中选择列。加文的例子帮助我走上了正确的道路。