4

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.

yaswanth
  • 2,349
  • 1
  • 23
  • 33
  • You want to order the result by the sum(colB) in the select column? If so, did you try with builder.desc(builder.sum(root.get(orderCol))) where orderCol points to colB ? – OTM May 01 '17 at 06:29
  • I want to use an alias to do that. Because I don't know at runtime if the order by column is going to be a sum column or a normal one. That's why I have created the alias for sum columns as the column name itself. – yaswanth May 01 '17 at 06:33
  • Okay, just thinking aloud. Did you try giving the alias name "colB" to someother name that do not match with any of the column names ? – OTM May 01 '17 at 06:59
  • ``It takes that much code to generate a simple SQL statement?? Obviously it having that much code helps in getting it wrong.`` – Rick James May 01 '17 at 17:21
  • This much code makes the query dynamic. – yaswanth May 02 '17 at 01:51
  • Did you get a chance to try with my suggestion in my previous comment ? – OTM May 02 '17 at 06:13
  • @OTM I tried doing that. root.get(aliasName) is returning null in any of the subsequent calls. I think the alias we are creating is not for the query but for the tuple response. – yaswanth May 02 '17 at 06:15
  • Yes the aliase name should be for the tuple result. But you can try the aliase name as something else other than the column name "colB" like "sumresultcol" in both select and order by. – OTM May 02 '17 at 06:34
  • I did that. When I try to refer to the alias by doing root.get(sumresultcol) during orderBy it is not fetching the actual column and instead returning null. Is there any other way you are suggesting? – yaswanth May 02 '17 at 06:59
  • You should not be doing root.get(aliasname). It should be root.get(actualcolumnname). In order by you should use "order by sumresultcol" – OTM May 03 '17 at 04:29
  • If I have the statement as orders.add(builder.desc(root.get(orderCol)));, then the actual column is picked up. Can you modify the above statement to what you are suggesting? – yaswanth May 03 '17 at 06:33
  • You can construct an expression for sum and use it select and order by clauses. Expression sum = builder.sum(root); and in select, selections.add(sum); and in order by, orders.add(builder.desc(sum); – OTM May 06 '17 at 06:03
  • What is the point of alias that we were discussing about in that case? Like other answers that have already been given this way, that's a workaround. I am expecting an answer by using the alias. That is the ask for this question. – yaswanth May 06 '17 at 09:18

2 Answers2

6

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)));
nickrak
  • 1,635
  • 15
  • 18
  • This is what I resorted to finally. For my use case, I don't know in advance if the orderingColumn should be summed. If I use an alias (in my case, I have the alias name same as the column name), I can pass the column name in the ordering column list and if this column was summed in the select column and aliased with the same name, I don't need to tell that this ordering column should be summed. – yaswanth May 04 '17 at 07:45
0

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")));
Piotr Rogowski
  • 3,642
  • 19
  • 24
  • 1
    Sorry @Abihabi87. I have missed declaring orderingColumns list. I have edited the question, You can check the updated code now. – yaswanth Apr 28 '17 at 10:04