java - Jooq select query using generated code and distinctOn

Question

I've been using the Jooq generated code pattern for queries like:

List<Model> results =
    dsl.selectFrom(TABLE_NAME)
       .where(TABLE_NAME.ID.eq(id))
       .fetchInto(Model.class);

This along with a RecordMapperProvider to determine which RecordMapper gets used based on the table and model class:

RecordMapperProvider recordMapperProvider = new RecordMapperProvider() {
            @Override
            public <R extends Record, E> RecordMapper<R, E> provide(final RecordType<R> recordType,
                                                                    final Class<? extends E> type) {
                return (RecordMapper<R, E>) recordMappers.getOrDefault(new RecordMapperKey(recordType, type),
                                                                       new DefaultRecordMapper<>(recordType, type));
            }
        };

This is an example of what I'm trying to accomplish:

List<Model> results =
    dsl.select(TABLE_NAME.fields())
       .distinctOn(TABLE_NAME.DIFF_COL)
       .from(TABLE_NAME)
       .where(buildConditions(criteria))
       .orderBy(TABLE_NAME.PKEY_COL,
                TABLE_NAME.TS_COL.desc())
       fetchInto(Model.class);

The new query includes the distinctOn component and it is changing the RecordType being passed into the RecordMapperProvider. This new RecordType doesn't match the generated code any longer.

My question is: Is there is a solution where I can keep the original RecordType from the generated code and still use this RecordMapperProvider pattern while using distinctOn in the query?

I've traversed the Jooq library to see how the RecordType objects are compared and found that their equality is based on the set of fields returned in the query. I'm thinking that if I can use a new comparator that would determine if one RecordType is a subset of another RecordType then it would still work. But, I'm finding that the Fields in the original query with the generated code don't match what is being returned in the query using distinctOn.

question from:https://stackoverflow.com/questions/65599570/jooq-select-query-using-generated-code-and-distincton

Answer 1

Found the answer here: https://www.jooq.org/doc/latest/manual/sql-execution/fetching/record-vs-tablerecord/

I was able to insert the raw Record into the TableRecord generated for the table I'm querying as such:

List<MyDisplayRecord> records =
dsl.select(TABLE_NAME.fields())
   .distinctOn(TABLE_NAME.DIFF_COL)
   .from(TABLE_NAME)
   .where(buildConditions(criteria))
   .orderBy(TABLE_NAME.PKEY_COL,
            TABLE_NAME.TS_COL.desc())
   fetchInto(MyTable.TABLE.getRecordType());
MyRecordMapper mapper = new MyRecordMapper();

return records.stream().map(mapper::map).collect(toList());

This way I was able to re-use the mapper with this query structure.