Mapping a one-to-many relationship to a list of records in jOOQ-CodePudding

I'm using MySQL 5.7, and I want to map a query with one-to-many relationships to a list of their representative record.

MySQL doesn't support multiset and JSON emulation doesn't work because it can't reference an alias two levels deep.

I'm wondering if there is another option for me to try mapping multiple relationships in one query.

record BulkContactEdit(long id, String organizationName, List <Contact> contacts) {}

record Contact(long id) {}

var bce = BULK_CONTACT_EDIT.as("bce");
var bceContacts = BULK_CONTACT_EDIT_CONTACTS.as("bceContacts");

var record =
    jooq()
    .select(
        bce.ID,
        bce.ORGANIZATION_NAME,
        DSL.multiset(
            jooq().select(RELATION.ID)
            .from(RELATION)
            .where(RELATION.ID.eq(bceContacts.CONTACT_ID)))
        .as("contacts")
        .convertFrom(r - > r.map(mapping(Contact::new))))
    .from(
        bce
        .join(bceContacts)
        .on(bceContacts.BULK_CONTACT_EDIT_ID.eq(bce.ID)))
    .where(bce.ID.eq(Long.parseLong(content)));
.fetchOne(Records.mapping(BulkContactEdit::new));

select
  `bce`.`id`,
  `bce`.`organizationName`,
  (
    select coalesce(
      json_merge_preserve(
        '[]',
        concat(
          '[',
          group_concat(json_array(`v0`) separator ','),
          ']'
        )
      ),
      json_array()
    )
    from (
      select `Relation`.`id` as `v0`
      from `Relation`
      where `Relation`.`id` = `bceContacts.contact_id`
    ) as `t`
  ) as `contacts`
from `BulkContactEdit` as `bce`
  join `BulkContactEditContacts` as `bceContacts`
    on `bceContacts`.`bulkContactEdit_id` = `bce`.`id`
where `bce`.`id` = 7;

CodePudding user response：

If you're not going to nest things further, you can still use MULTISET_AGG as follows:

jooq().select(
           bce.ID, 
           bce.ORGANIZATION_NAME,
           multisetAgg(RELATION_ID)
               .as("contacts")
               .convertFrom(r - > r.map(mapping(Contact::new)))
       )
      .from(bce)
      .join(bceContacts)
          .on(bceContacts.BULK_CONTACT_EDIT_ID.eq(bce.ID))
      .leftJoin(RELATION)
          .on(RELATION.ID.eq(bceContacts.CONTACT_ID))
      .where(bce.ID.eq(Long.parseLong(content))
      .groupBy(bce.ID, bce.ORGANIZATION_NAME)
      .fetch(Records.mapping(BulkContactEdit::new));

Keeping in mind that MULTISET_AGG produces NULL, not an empty list if an organisation doesn't have any contacts.