Is there an easy way to get a ParentRecord plus List<ChildRecord> in jooq?

user_97901 0
Viewed 27

Looking at the Sakila database I wonder how could I write a query that gets me a Country plus all Cities in the country.

I imagine it returning Result<Record2<CountryRecord,List>. Does that make sense? I might want a country plus all cities attached to it in one jooq query? I assume it might use the multiset under the covers?

jooq
1 Answers

Solution without MULTISET

In your particular case, it doesn't seem you need to use MULTISET, you can just use a classic join based solution, e.g.

Map<CountryRecord, Result<CityRecord>> map = ctx
    .select()
    .from(CITY)
    .join(COUNTRY).on(CITY.COUNTRY_ID.eq(COUNTRY.COUNTRY_ID))
    .fetchGroups(COUNTRY, CITY);

It's creating a Map<CountryRecord, Result<CityRecord>>, which is almost the same as what you requested.

jOOQ 3.17 solution using MULTISET

In jOOQ 3.17, #4727 was implemented allowing to use any Table<R> reference as a SelectField<R> to project table expressions just like in PostgreSQL, producing the generated nominal record types instead of the strucural Record[N] types. You can then write:

Result<Record2<CountryRecord, Result<CityRecord>>> result = ctx
    .select(

        // You can project any table expression here
        COUNTRY,
        multiset(
            selectFrom(CITY)
                .where(CITY.COUNTRY_ID.eq(CITY.COUNTRY_ID))
        ))
    .from(COUNTRY)
    .fetch();

This is the exact type you wanted (Result extends List, so I'm assuming this is fine). Different types can be created by using different kinds of ad-hoc conversion

See also: https://www.jooq.org/doc/dev/manual/sql-building/table-expressions/tables-as-selectfield/

jOOQ 3.16 solution using MULTISET

Without the above means to project a generated record, you can do this to project them manually:

Result<Record2<CountryRecord, Result<CityRecord>>> result = ctx
    .select(

        // Manually nest all columns of a table in a nested record
        row(COUNTRY.fields()).convertFrom(r -> r.into(COUNTRY)),
        multiset(
            selectFrom(CITY)
                .where(CITY.COUNTRY_ID.eq(CITY.COUNTRY_ID))
        ))
    .from(COUNTRY)
    .fetch();

This obviously still works in jOOQ 3.17 as well

user_5217990
Related