How to fetch groups ordered by aggregative value, using jooq

Question

I want to implement this query using jooq:

SELECT bank_id, array_agg(id)
FROM aggregative_line_item
GROUP BY bank_id
ORDER BY sum(amount) desc;

Now, jooq provides fetchGroups(), that perfectly maps values to the Map<String, List>

 create.selectFrom(aggregative_line_item)
            .fetchGroups(aggregative_line_item.bank_id, aggregative_line_item.id);

However, I don't understand how to order the returned results by sum() value.

Answer 1

When you use ResultQuery.fetchGroups(), then you're grouping result sets in Java, not in SQL. There are a lot of legitimate cases for this, but in your case, I think you should do everything in SQL directly, assuming you're using PostgreSQL, because you already used ARRAY_AGG() in your SQL query. Just write:

Result<Record2<String, Integer[]>> result =
create.select(AGGREGATIVE_LINE_ITEM.BANK_ID, arrayAgg(AGGREGATIVE_LINE_ITEM.ID))
      .from(AGGREGATIVE_LINE_ITEM)
      .groupBy(AGGREGATIVE_LINE_ITEM.BANK_ID)
      .orderBy(sum(AGGREGATIVE_LINE_ITEM.AMOUNT).desc())
      .fetch();

This assumes the usual static imports, including:

import static org.jooq.impl.DSL.*;

The result is not exactly in the form you were looking for, but you can still call fetchMap() on the result, e.g.

Map<String, Integer[]> map = result.fetchMap(
  AGGREGATIVE_LINE_ITEM.BANK_ID, 
  arrayAgg(AGGREGATIVE_LINE_ITEM.ID)
);

Or, instead of repeating the expressions, you could assign them to local variables and reuse them:

Field<String> key = AGGREGATIVE_LINE_ITEM.BANK_ID;
Field<Integer[]> value = arrayAgg(AGGREGATIVE_LINE_ITEM.ID);

// And then:
Map<String, Integer[]> map = 
create.select(key, value)
      .from(AGGREGATIVE_LINE_ITEM)
      .groupBy(AGGREGATIVE_LINE_ITEM.BANK_ID)
      .orderBy(sum(AGGREGATIVE_LINE_ITEM.AMOUNT).desc())
      .fetchMap(key, value);

Still, if you prefer the List<Integer> type over the Integer[] type, you could use the JDK Collector APIs on ResultQuery.collect(), instead:

Map<String, List<Integer>> map = 
create.select(AGGREGATIVE_LINE_ITEM.BANK_ID, arrayAgg(AGGREGATIVE_LINE_ITEM.ID))
      .from(AGGREGATIVE_LINE_ITEM)
      .groupBy(AGGREGATIVE_LINE_ITEM.BANK_ID)
      .orderBy(sum(AGGREGATIVE_LINE_ITEM.AMOUNT).desc())
      .collect(Collectors.toMap(
         r -> r.value1(),
         r -> Arrays.asList(r.value2())
      ));