Search code examples
jooq

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

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?

Solution

  • 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