hibernate - ORDER BY 在 Hibernate JPA 2 中使用子查询

Question

我正在 hibernate-jpa-2.1 中将 NamedQuery 重写为 CriteriaQuery。原始 NamedQuery 包含一个 订购 引用别名子查询的子句。
select
new ItemDto (
item.id,
item.number,
(select count(*) from ClickEntity as click where click.item.id = item.id) as clickCount
)
from ItemEntity as item
order by clickCount desc

我找不到任何使用别名来引用 clickCount 字段的方法，所以我想我不妨在两个地方都使用子查询:

public List<ItemDto> getItems() {
    ...
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<ItemDto> query = criteriaBuilder.createQuery(ItemDto.class);
    Root<ItemEntity> item = query.from(ItemEntity.class);

    query
        .select(
            cb.construct(ItemDto.class,
                item.get("id"),
                item.get("number"),
                getClickCount(cb, query, item).getSelection()
            )
        )
        .orderBy(cb.desc(getClickCount(cb, query, item).getSelection()))

    TypedQuery<ItemDto> typedQuery = entityManager.createQuery(query);
    return typedQuery.getResultList();
}

private Subquery<Long> getClickCount(CriteriaBuilder cb, CriteriaQuery<ItemDto> query, Root<ItemEntity> item) {
    Subquery<Long> subquery = query.subquery(Long.class);
    Root<ClickEntity> click = subquery.from(ClickEntity.class)

    return subquery
        .select(cb.count(click.get("id")))
        .where(cb.equal(click.get("item").get("id"), item.get("id")));
}

但是，在调用 getItems() 时，Hibernate 在创建 TypedQuery 时抛出以下异常:

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: query [...]

解析后的查询如下所示:

select new ItemDto(
    generatedAlias0.id,
    generatedAlias0.number,
    (select count(generatedAlias1.id) from ClickEntity as generatedAlias1 where( generatedAlias1.item.id=generatedAlias0.id ))
)

from ItemEntity as generatedAlias0 

order by
    (select count(generatedAlias2.id) from ClickEntity as generatedAlias2 where( generatedAlias2.item.id=generatedAlias0.id )) desc

尽管抛出了错误，但这个查询对我来说看起来不错。我已经在没有 order by 子句的情况下对其进行了测试，然后它按预期工作，因此错误肯定是由该子句引起的。但是，由于子查询显然有效，我很难弄清楚问题是什么。

我尝试/考虑过的:

使用@PostConstruct 设置ItemEntity 的@Transient 字段；这不是一个选项，因为在实际应用中，clickCount 的值取决于 Date 参数。

检索结果后排序；这不是一个选项，因为排序需要在应用(可选)限制参数之前发生

不使用 getSelection()。这具有相同的效果(即使是相同的查询)。

所以，我想知道，Hibernate 是否真的支持这种方法，或者我是否缺少一个(可能更简单的)替代方法来使用子查询的结果作为排序参数？

Answer 1

我找到了解决这个问题的两种选择，这两种方法都会产生不同的结果。请注意，由于在 select 子句中使用了聚合函数，因此两者都需要 分组 未通过聚合选择的每一列的子句。

1. 用 where 子句交叉连接

为查询创建额外的根将导致交叉联接。结合 where 子句，这将导致内部联接，而您仍然可以访问根中的字段。添加更多 where 子句允许进一步过滤。

public List<ItemDto> getItems() {
    ...
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<ItemDto> query = criteriaBuilder.createQuery(ItemDto.class);
    Root<ItemEntity> item = query.from(ItemEntity.class);
    //Extra root here
    Root<ClickEntity> click = query.from(ClickEntity.class);

    query
        .select(
            cb.construct(ItemDto.class,
                item.get("id"),
                item.get("number"),
                cb.count(click.get("id"))
            )
        )
        //Required to make the cross join into an inner join
        .where(cb.equal(item.get("id"), click.get("item").get("id")))
        //Required because an aggregate function is used in the select clause
        .groupBy(item.get("id"), item.get("number"))
        //Possibility to refer to root 
        .orderBy(cb.count(click.get("id")));
    ...
}

由于这是一个内部联接，因此此方法仅选择单击表中的单击实体所引用的项目实体。换句话说，不会选择点击次数为 0 的项目。如果需要过滤没有点击的项目，这是一种有效的方法。

2.在ItemEntity中添加一个字段

通过向引用单击实体的 ItemEntity 添加一个 @OneToMany 字段，可以创建左联接。首先，更新 ItemEntity:

@Entity
public class ItemEntity {
    ...
    @OneToMany(cascade = CascadeType.ALL)
    //The field in the click entity referring to the item
    @JoinColumn(name="itemid")
    private List<ClickEntity> clicks;
    ...
}

现在，您可以让 JPA 为您执行联接并使用联接来引用 ClickEntity 中的字段。此外，您可以使用 join.on(...) 向连接添加额外的条件，并且使用 query.have() 将允许您像第一种方法一样在没有点击的情况下过滤掉项目。

public List<ItemDto> getItems() {
    ...
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<ItemDto> query = criteriaBuilder.createQuery(ItemDto.class);
    Root<ItemEntity> item = query.from(ItemEntity.class);
    //Join on the clicks field. A left join also selects items with 0 clicks.
    Join<ItemEntity, ClickEntity> clicks = item.join("clicks", JoinType.left);
    //Use join.on if you need more conditions to the join
    /*clicks.on(...) */

    query
        .select(
            cb.construct(ItemDto.class,
                item.get("id"),
                item.get("number"),
                cb.count(clicks.get("id"))
            )
        )
        //Required because an aggregate function is used in the select clause
        .groupBy(item.get("id"), item.get("number"))
        //Uncomment to filter out items without clicks
        /* .having(cb.gt(cb.count(clicks.get("id")), 0)) */
        //Refer to the join
        .orderBy(cb.count(clicks.get("id")));
    ...
}

请注意不要内联 clicks 变量，因为这将有效地将 clicks 表连接到 items 表上两次。

最后，第二种方法最适合我的情况，因为我也想拥有没有点击的项目，并且找不到将交叉连接变成左外连接的简单方法。