I have the following SQL query.
select colA, sum(colB) as colB from tableA group by colA order by colB desc; 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(); 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; 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
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
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?
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.
2 Answers
Answers 1
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))); Answers 2
i think so you have bug in orderingColumns, you schould add colA instead colB
or instead
for (String orderCol : orderingColumns) { orders.add(builder.desc(root.get(orderCol))); } you can use
orders.add(builder.desc(root.get("colA"))); 
0 comments:
Post a Comment