如何使用LEFT OUTER JOIN进行JPA查询 [英] How to make a JPA query with LEFT OUTER JOIN

问题描述

基于这两个表(及其对应的实体):

Based on these two tables (and their corresponding entities) :

profiles(id, email, name)
items(id, profile_id, rank, price)

Profile(email, name)
Item(profile, rank, price)

我必须列出所有配置文件，按其项目的最佳顺序排序(实际上，这是顶级配置文件"列表).

I have to list all the profiles, ordered by the best rank of their items (it's a "top profile" list in fact).

这是SQL请求，例如您可以在PHPMyAdmin中执行它:

Here is the SQL request like you can execute it in PHPMyAdmin for example:

SELECT AVG(i.rank) AS rank, p.* FROM profiles AS p LEFT OUTER JOIN items AS i ON p.id = i.profile_id GROUP BY p.id ORDER BY rank DESC;

我是JPA的新手，我找不到一些使用CriteriaBuilder进行LEFT OUTER JOIN的示例(如果这样做是对的).

I'm new to JPA and I can't find some examples about doing a LEFT OUTER JOIN with CriteriaBuilder (if it's the right thing to do).

如果有人能引导我走上正确的道路，我将不胜感激(我不是要求别人做我的工作，只是有很好的线索).

I would really appreciate if someone would lead me to the right way (I'm not asking someone to do me the job, just having good clues).

非常感谢！ :)

推荐答案

此查询可以用JPQL表示如下:

This query can be expressed in JPQL as follows:

SELECT p, AVG(i.rank) as rank
FROM Item i RIGHT JOIN i.profile p
GROUP BY p
ORDER BY rank DESC

请注意，您不能在JPA中编写任意JOIN，只能在关系上使用JOIN，因此此处需要RIGHT JOIN，因为您的关系是单向的，关系的源应位于左侧一侧.

Note that you can't write arbitrary JOINs in JPA, only JOINs over relationships, therefore you need a RIGHT JOIN here, because your relationship is unidirectional and source of the relationship should be at the left side.

此查询可以用非常简单的方式在JPA 2.0 Criteria API中进行翻译，但是，我上次检查它时，Hibernate的Criteria API的实现不支持正确的联接(并且Play框架在下面使用了Hibernate).因此，我认为您必须在这种情况下使用JPQL查询.

This query could be translated in JPA 2.0 Criteria API in pretty straightforward way, however, the last time I checked it Hibernate's implementation of Criteria API didn't support right joins (and Play Framework uses Hibernate underneath). So, I think you have to use JPQL query in this case.

这篇关于如何使用LEFT OUTER JOIN进行JPA查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！