ORDER BY using a subquery in Hibernate JPA 2

Question

I am rewriting a NamedQuery to a CriteriaQuery in hibernate-jpa-2.1. The original NamedQuery contains an order by clause that refers to an aliased subquery.

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

I could not find any way to use the alias to refer to the clickCount field, so I figured I might as well use the subquery in both places:

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

However, when calling getItems(), Hibernate throws the following exception upon creation of the TypedQuery:

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

The parsed query looks like:

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

Despite the error thrown, this query looks fine to me. I've tested it without the order by clause and then it works as expected, so the error is definitely caused by that clause. However, since the Subquery obviously works, I have a hard time figuring out what the problem is.

What I tried / considered:

• Using @PostConstruct to set a @Transient field of the ItemEntity; this is not an option as, in the actual application, the value of clickCount depend on a Date parameter.
• Ordering after retrieving the results; this is not an option as the ordering needs to happen before an (optional) limit parameter is applied
• Not using getSelection(). This has the same effect (even the same query).

So, I wonder, is this approach actually supported by Hibernate or am I missing a (possibly simpler) alternative to use the result of a Subquery as ordering parameter?

Answer 1

I found two options for solving this problem, both of which will yield different results. Note that since an aggregate function is used in the select clause, both require a group by clause for each column not selected via an aggregate.

1. Cross join with a where clause

Creating an extra root for the query will result in a cross join. Combined with a where clause this will result in an inner join, while you still have access to the field in the root. Adding more where clauses allows for further filtering.

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

Since this is an inner join, this approach selects only item entities that are referred to by click entities in the click table. In other words, items with 0 clicks are not selected. This is a valid approach if items without clicks need to be filtered.

2. Adding a field to the ItemEntity

By adding an @OneToMany field to the ItemEntity that refers to the click entity, it is possible to create a left join. First, update the 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;
    ...
}

Now, you can have JPA perform the join for you and use the join to refer to fields in the ClickEntity. Furthermore, you can add extra conditions to the join using join.on(...) and using query.having() will allow you to filter out items without clicks as in the first approach.

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

Do pay attention not to inline the clicks variable, as this will effectively join the clicks table on the items table twice.

In the end, the second approach worked best for my case since I wanted to have items without clicks as well and could find no straightforward method to turn the crossjoin into a left outer join.