Referring to an earlier aliased field in a criteria query

Question

In this query:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Tuple> q = cb.createTupleQuery();

// FROM GamePlayedEvent gpe
Root<GamePlayedEvent> gpe = q.from(GamePlayedEvent.class);
// SELECT gameId, COUNT(*) AS count, AVG(duration)
// AS avDur, AVG(rewardCurrency) AS avCur, AVG(rewardXP) avXp
q.select(cb.tuple(
    gpe.<String>get("gameId"),
    cb.count(gpe).alias("count"),
    cb.avg(gpe.<Double>get("duration")).alias("avDur"),
    cb.avg(gpe.<Integer>get("rewardCurrency")).alias("avCur"),
    cb.avg(gpe.<Integer>get("rewardXp")).alias("avXp")
));
// WHERE loginTime BETWEEN ...
q.where(cb.between(gpe.<Date>get("time"), fromTime, toTime));
// GROUP BY gameId
q.groupBy(gpe.<String>get("gameId"));
// ORDER BY count DESC
q.orderBy(cb.desc(???));

How can I add the ORDER BY count DESC, referring to the "count" defined in the SELECT clause?

Answer 1

What if you just captured the count expression, and used it directly?

Expression event_count = cb.count(gpe);

q.select(cb.tuple( 
  gpe.<String>get("gameId"), 
  event_count,
  ... 
)); 

q.orderBy(cb.desc(event_count));

Answer 2

I came across the same problem today but none of the suggested solutions worked for me because I needed to reuse the expression not only in the order by clause but also in the group by clause. One obvious solution would be to create a view on the database level but this is a bit clumsy, creates an unnecessary subquery and even not possible if the db user isn't granted enough privileges. A better option which I ended up implementing is to write something like this

q.select(cb.tuple( 
  gpe.<String>get("gameId"), 
  cb.count(gpe),
  ... 
)).groupBy(cb.literal(2)).orderBy(cb.literal(2));

The first downside of this approach is that the code is errorprone. The other drawback is that the generated sql query contains ordinal position notation, which works on some databases (like postgresql or mariadb) but doesn't work on others (like sql server). In my case, however, I found this to be the best option.

Tested on jpa 2.1 with hibernate 5.2.3 as a provider and postgresql 9.6.

Answer 3

Even though the Pro JPA 2 book describes that the alias method can be used to generate a sql query alias (on page 251) I have had no success with making it work with neither EclipseLink or Hibernate. For your question I would say that your orderBy line should read:

q.orderBy(cb.desc(cb.count(gpe));

if it was supported by the different vendors.

As far as my research goes it seams that the alias method is only used for naming elements in the tuble used in the select (so only for projection).

I have one question though. Why would you want to use the JPA Criteria API for this query. It (the query) seams to be static in nature so why not use JPQL where you can define your query aliases directly.