How can I map entity using a Jooq?

I have a trouble: can't figure out how to map result of Jooq query.

I have 2 entities: Payment method and Currency for it. Payment method contains a List inside. Both are stored in different tables.

I get one:

public Optional<PaymentMethod> getMethod(Long id) {
return this.dslContext.selectFrom(PAYMENT_METHOD)
    .where(
        PAYMENT_METHOD.ID.eq(id),
        PAYMENT_METHOD.DELETED.eq(false)
    ).fetchOptional().map(v -> methodDao.mapper().map(v));

And want to get currencies too, by using a:

return this.dslContext.select(asterisk())
    .from(PAYMENT_METHOD)
    .join(PAYMENT_METHOD_CURRENCY)
    .on(PAYMENT_METHOD_CURRENCY.METHOD_ID.eq(id))
    .where(
        PAYMENT_METHOD.ID.eq(id),
        PAYMENT_METHOD.DELETED.eq(false)
    )
    .fetch()
    .map(...)...

So, how can I map this further?

I have a decision for a single entity inside other, like that:

private PaymentProviderMethod mapMethod(Record record) {
final PaymentMethodRecord methodRecord = record.into(PAYMENT_METHOD);
final PaymentProviderRecord providerRecord = record.into(PAYMENT_PROVIDER);
return new PaymentProviderMethod(
    methodDao.mapper().map(methodRecord),
    providerDao.mapper().map(providerRecord));

But have no idea how to map list in this case. Could u help, please?

Solution

If you want to nest collections in with jOOQ, then you're probably looking for the MULTISET or MULTISET_AGG operator along with ad-hoc conversion, which allows for type safe nesting collections directly in SQL, if your database product support SQL/XML or SQL/JSON.

The following might not be the exact query you were looking for, but you get the idea:

// Assuming the usual static import:
import static org.jooq.impl.DSL.*;

ctx.select(
       PAYMENT_METHOD.ID,
       PAYMENT_METHOD.PROVIDER_ID,
       PAYMENT_METHOD.NAME,
       ...
       multiset(
           select(
               PAYMENT_METHOD_CURRENCY.ID,
               PAYMENT_METHOD_CURRENCY.NAME,
               ...
           )
           .from(PAYMENT_METHOD_CURRENCY)
           .where(PAYMENT_METHOD_CURRENCY.METHOD_ID.eq(PAYMENT_METHOD.ID))
       ).convertFrom(r -> r.map(Records.mapping(Currency::new)))
   )
   .from(PAYMENT_METHOD)
   .fetch(Records.mapping(PaymentMethod::new))

The above example uses MULTISET for a nested, correlated subquery, but you coudl do the same thing with MULTISET_AGG and a JOIN and GROUP BY clause, especially if your dialect doesn't support correlating derived tables, see issue #12045. The whole query is type safe. If you change your PaymentMethod or Currency DTO classes, the query will stop compiling until you fix your projections.