JPA Criteria Builder 按总和列排序 [英] JPA Criteria Builder order by a sum column

问题描述

我有以下 SQL 查询.

I have the following SQL query.

select colA, sum(colB) as colB from tableA group by colA order by colB desc;

我使用 jpa 标准构建器动态构建查询.

I have used jpa criteria builder to build the query dynamically.

List<String> selectCols = new ArrayList<>();
selectCols.add("colA");
List<String> sumColumns = new ArrayList<>();
sumColumns.add("colB");
List<String> groupByColumns = new ArrayList<>();
groupByColumns.add("colA");
List<String> orderingColumns = new ArrayList<>();
orderingColumns.add("colB");

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> cQuery = builder.createQuery(Tuple.class);
Root<T> root = cQuery.from(getDomainClass());

List<Selection<?>> selections = new ArrayList<>();
for (String column : selectCols) {
    selections.add(root.get(column).alias(column));
}

if (sumColumns != null) {
    for (String sumColumn : sumColumns) {
        selections.add(builder.sum(root.get(sumColumn)).alias(sumColumn));
    }
}

cQuery.multiselect(selections);

List<Expression<?>> groupByExpressions = new LinkedList<>();

for (String column : groupByColumns) {
    groupByExpressions.add(root.get(column));
}

cQuery.groupBy(groupByExpressions);

List<Order> orders = new ArrayList<>();

for (String orderCol : orderingColumns) {

    orders.add(builder.desc(root.get(orderCol)));

}

cQuery.orderBy(orders);

entityManager.createQuery(cQuery).getResultList();

我调试并检查了正在执行的确切 sql 语句.

I debugged and checked the exact sql statement that is getting executed.

select colA as col_1_0, sum(colB) as col_2_0 from tableA group by colA order by colB desc;

正在执行的结果 sql 语句没有 colB 作为 colB 的别名.我正在使用 MySql.令人惊讶的是，当 sql 模式设置为 NO_ENGINE_SUBSTITUTION 时，该语句被执行并且我得到了正确的结果.但是当sql模式为ONLY_FULL_GROUP_BY时，​​数据库抛出错误说

The resultant sql statement that is getting executed doesn't have the alias for colB as colB. I am using MySql. Surprisingly, this statement gets executed and I get proper results when sql mode is set to NO_ENGINE_SUBSTITUTION. But when the sql mode is ONLY_FULL_GROUP_BY, the database throws an error saying

ORDER BY 子句的表达式 #1 不在 GROUP BY 子句中，并且包含非聚合列colB"，该列在功能上不起作用依赖于 GROUP BY 子句中的列；这与sql_mode=only_full_group_by

Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'colB' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

我知道在这种模式下，sql 会验证查询，而我的查询失败.如何使用 jpa 条件构建器创建别名，以便执行的实际语句包含 sum 列的正确别名?

I understand that in this mode, sql validates the query and my query fails. How do I create an alias with jpa criteria builder so that the actual statement getting executed contains a proper alias for the sum column?

注意:我使用 spring-data-jpa，这是存储库的自定义实现.我可以使用 @Query 注释手动编写查询，但查询的性质在 group by、order by、where 子句和 select 列上是动态的.因此需要动态标准构建器实现.

Note: I use spring-data-jpa and this is a custom implementation of repository. I can manually write the query using @Query annotation but the nature of the queries are dynamic on group by, order by, where clauses and the select columns. Hence the need for a dynamic criteria builder implementation.

推荐答案

尝试对聚合进行排序.根据我的经验，列别名通常不能在查询本身中引用.

Try sorting over the aggregate. In my experience, column aliases usually can't be referenced in the query itself.

orders.add(builder.desc(builder.sum(sumColumn)));

这篇关于JPA Criteria Builder 按总和列排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！