How to write JOOQ query to get only one java record containing all results

Question

I have the following java records :

record Info(Integer count){}
record Person(Integer id, String name) {}
record MyResult(Info info, List<Person> persons){}

I need to return a MyResult Object containing the total count of persons in Info and a partial list of these persons.

I have no idea if it is possible to do it directly with only one JOOQ query and how to do it.

Here is an attempt but obviously it will fetch a list so this is not the correct way.

DSL.getContext(conn)
  .select(
    DSL.row(DSL.count().over()).mapping(Info::new),
    DSL.multiset(
      DSL.select(
         PERSON.PERSON_ID,
         PERSON.NAME
      ).convertFrom(r -> r.map(Records.mapping(PERSON::new)))
    )
  )
   .from(PERSON)
   .where(PERSON.NAME.startWith("A"))
   .orderBy(PERSON.NAME.asc())
   .offset(0)
   .limit(5) 
   .fetchOne(Records.mapping(MyResult::new));

Any idea ?

Answer 1

The problem here is SQL's logical order of operations. Any means of counting results (both aggregate counts and window counts) happens before the LIMIT is applies. So, if you don't want LIMIT to have any effect on your count, you have to write the query in a way that it counts first. But if you don't get any results from your WHERE clause, then you don't get any rows when using window counts. When using aggregate counts, you'd get a row, but you can't apply LIMIT so easily anymore. (Without the LIMIT, you could simply use aggregate count and multisetAgg() instead).

So, I can see 2 solutions, a slow but simple one, a faster but more complex one:

Doing everything in the query, accessing the table twice:

If you want to move all the mapping logic into the query itself, I suspect you'd have to run 2 queries against the PERSON table, which probably isn't desirable for performance reasons:

ctx.select(
      count().filterWhere(PERSON.NAME.startsWith("A")).convertFrom(Info::new),
      multiset(
        select(PERSON_ID, PERSON_NAME)
        .from(PERSON)
        .where(PERSON.NAME.startsWith("A"))
        .orderBy(PERSON.NAME.asc())
        .limit(5)
      ).convertFrom(r -> r.map(mapping(Person::new)))
    )
   .from(PERSON)
   .fetchOne(mapping(MyResult::new))

This works for your simple example, but as soon as you start joining things, you will have to duplicate a lot of query logic between the inner and outer query, and that's not really desirable.

Using JDK Collector API

But you don't have to form the query in a hierarchical way like your target data structure. You can still do some mapping logic in the client as well, outside of jOOQ, e.g.:

ctx.select(
      count().over().convertFrom(Info::new),
      row(PERSON_ID, PERSON_NAME).mapping(Person::new)
    )
    .from(PERSON)
    .where(PERSON.NAME.startsWith("A"))
    .orderBy(PERSON.NAME.asc())
    .limit(5)
    .collect(collectingAndThen(
        groupingBy(
            // There's a single possible value from count().over()
            r -> r.value1(),
            mapping(r -> r.value2(), toList())
        ),
        m -> {
            // Probably, just use var here...
            Iterator<Entry<Info, List<Person>>> it = m.entrySet().iterator();

            if (it.hasNext()) {
                Entry<Info, List<Person>> e = it.next();
                return new MyResult(e.getKey(), e.getValue());
            }
            else
                return new MyResult(new Info(0), Collections.emptyList());
        }
    )));

This is assuming the following static import:

import static java.util.stream.Collectors.*;

Granted, it's not so pretty. You could maybe refactor this into a different format or write some more generic utility for this sort of result, but I hope you get the essence of it.

Note: this approach won't give you the correct count value if you paginate beyond the last page