在Hibernate JPA 2中使用子查询进行ORDER BY

Question

我正在将一个NamedQuery重写为CriteriaQuery，使用的是hibernate-jpa-2.1。原始的NamedQuery包含一个order by子句，它引用了一个别名子查询。

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 的值取决于一个日期参数。
• 在检索结果后进行排序；但这也不可行，因为需要在应用（可选的）限制参数之前进行排序。
• 不使用 getSelection()。这会产生相同的效果（甚至是相同的查询）。

因此，我想知道，Hibernate 是否支持这种方法，或者我是否遗漏了一种（可能更简单的）替代方案来使用子查询的结果作为排序参数？

- EJJ

Answer 1

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

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")));
    ...
}

由于这是一个内连接，该方法仅选择在click表中被click实体引用的item实体。换句话说，没有点击的项目不会被选中。如果需要过滤没有点击的项目，则这是一种有效的方法。

2. 向ItemEntity添加字段

通过向ItemEntity添加一个@OneToMany字段来引用click实体，可以创建一个左连接。首先，更新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.having()来过滤掉没有点击的项，就像第一种方法一样。

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")));
    ...
}

请注意不要内联点击变量，否则将会在项目表上有效地加入两次点击表。

最终，第二种方法对我的情况效果最好，因为我想要没有点击的项目，并且找不到将交叉连接转换为左外连接的简单方法。

- EJJ