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?

Astern

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

Forenoon

What if you need the reference the column but a specific name though? Not just reevaluate the expression in the different query parts – Farmstead

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.

Ladoga

Thank you, it saved me. For some reason Hibernate doesn't like grouping by subquery it already has in select list. – Clemons

Great! this is only solution fin for alias in group by clause. – Madonia

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.

Eldaelden

Answer 4

Have you tried setting up a projection with an alias?

criteria.setProjection(Projections.projectionList()
   .add(Projections.count("item.id"), "countItems"));
criteria.addOrder(Order.desc("countItems"));

Phycomycete

That's the Hibernate Criteria API. This question is about the JPA 2 Criteria API (and it's Hibernate implementation). Confusingly enough, those are not the same thing. – Candent

Answer 5

For a sum aggregation field I have the following code which worked for me:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<T> cq = cb.createQuery(entity);
Root<T> root = cq.from(entity);

cq.orderBy(cb.desc(cb.sum(root.get(orderByString))));

// orderByString is string entity field that is being aggregated and which we want to put in orderby clause as well. 

Koniology