ORDER BY using a subquery in Hibernate JPA 2
<p>I am rewriting a NamedQuery to a CriteriaQuery in hibernate-jpa-2.1. The original NamedQuery contains an <strong>order by</strong> clause that refers to an aliased subquery.</p>

<p><code>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
</code></p>

<p>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:</p>

<pre><code>public List&lt;ItemDto&gt; getItems() {
    ...
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery&lt;ItemDto&gt; query = criteriaBuilder.createQuery(ItemDto.class);
    Root&lt;ItemEntity&gt; 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&lt;ItemDto&gt; typedQuery = entityManager.createQuery(query);
    return typedQuery.getResultList();
}

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

    return subquery
        .select(cb.count(click.get("id")))
        .where(cb.equal(click.get("item").get("id"), item.get("id")));
}
</code></pre>

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

<pre><code>org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: query [...]
</code></pre>

<p>The parsed query looks like:</p>

<pre><code>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
</code></pre>

<p>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.</p>

<p>What I tried / considered:</p>

<ul>
<li>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.</li>
<li>Ordering after retrieving the results; this is not an option as the ordering needs to happen before an (optional) limit parameter is applied</li>
<li>Not using getSelection(). This has the same effect (even the same query).</li>
</ul>

<p>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?</p>