I have following DTOs:

• ProductDTO

• UserDTO

• UserRoleDTO:

• I need to fetch a list of Products and each of those Products has a field “creatorId” (NULLABLE).

  • For the creatorId i need to fetch the corresponding User (1:1 relationship),
    • and for this User i need to fetch his roles (1:N relationship)

Product -> 1:1 -> User -> 1:N -> UserRoles

I got this to work with following jooq query:

dsl()
    .select(
            asterisk(),
            multiset(
                    selectDistinct(
                            asterisk()
                    )
                            .from(UserRole.USER_ROLE)
                            .where(UserRole.USER_ROLE.USERID.eq(Product.PRODUCT.CREATORID))
            ).as("roles")),
    .from(Product.PRODUCT)
        .leftJoin(User.USER)
        .on(User.USER.USERID.eq(Product.PRODUCT.CREATORID))
    .where(...)
    .and(Product.PRODUCT.CLIENTID.eq(request().getClientId()))
    .groupBy(Product.PRODUCT.PRODUCTID)
    .orderBy(...)
    .offset(...)
    .limit(...)
    .fetch().map(new RecordMapper<Record, D>() {
        @Override
        public @Nullable D map(Record record) {
            ProductDTO product = rec.into(ProductDTO.class);
            UserDTO creator = rec.into(UserDTO.class);
            List<UserRoleDTO> creatorRoles = rec.get("roles", Result.class).into(UserRoleDTO.class);
            creator.setRoles(creatorRoles);
            product.setCreator(creator);

            return product;
        }
    });

Now my question:

is this also possible without the explicit “RecordMapper” part ?

Im already satisfied i got this to work, as i only found examples for using multiset with direct 1:N relationships, but what i have here is a 1:1 relationship that (on a deeper level) also contains a 1:N relationship.