在条件查询中引用先前的别名字段

发布于 2024-10-10 01:37:29 字数 926 浏览 0 评论 0原文

在此查询中：

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

如何添加 ORDER BY count DESC，引用 SELECT 子句中定义的“count”？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

甜是你 2024-10-17 01:37:29

如果您只是捕获计数表达式并直接使用它怎么办？

Expression event_count = cb.count(gpe);

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

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

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

回复收藏 0 原文

℡寂寞咖啡 2024-10-17 01:37:29

我今天遇到了同样的问题，但建议的解决方案都不适合我，因为我不仅需要在 order by 子句中重用表达式，而且还需要在 group by 中重用该表达式条款。
一种明显的解决方案是在数据库级别创建视图，但这有点笨拙，会创建不必要的子查询，如果没有授予 db 用户足够的权限，甚至是不可能的。我最终实现的一个更好的选择是编写类似这样的代码。

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

这种方法的第一个缺点是代码容易出错。另一个缺点是生成的 sql 查询包含序数位置表示法，它适用于某些数据库（如 postgresql 或 mariadb），但不适用于其他数据库（如 sql server）。然而，就我而言，我发现这是最好的选择。

在 jpa 2.1 上进行测试，使用 hibernate 5.2.3 作为提供程序和 postgresql 9.6。

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.

回复收藏 0 原文

策马西风 2024-10-17 01:37:29

尽管 Pro JPA 2 书描述了可以使用 alias 方法生成 sql 查询别名（第 251 页），但我没有成功使其与 EclipseLink 或 Hibernate 一起使用。对于你的问题，我想说你的 orderBy 行应该是：

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

如果它得到不同供应商的支持。

据我的研究表明，别名方法仅用于命名选择中使用的管中的元素（因此仅用于投影）。

不过我有一个问题。为什么要使用 JPA Criteria API 来执行此查询。它（查询）本质上是静态的，所以为什么不使用 JPQL 来直接定义查询别名。

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.

回复收藏 0 原文

国际总奸 2024-10-17 01:37:29

您是否尝试过使用别名设置投影？

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

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

回复收藏 0 原文

美胚控场 2024-10-17 01:37:29

对于总和聚合字段，我有以下对我有用的代码：

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.

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.

回复收藏 0 原文

~没有更多了~