1

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;
Disheart
  • 197
  • 1
  • 9
  • Whoops found an answer from Lukas by using JSON with DSL.field as explained [here](https://stackoverflow.com/questions/33845239/jooq-single-query-with-one-to-many-relationship). – Disheart Apr 13 '22 at 08:08

1 Answers1

1

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.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Is it also possible to do this for a one-to-one relationship? And could I map it to a jOOQ generated Record instead of a Java record? – Disheart Apr 13 '22 at 11:18
  • I also get this exception when I try `MULTISET_AGG`: `No Converter found for types org.jooq.Converters$UnknownType and java.util.List` – Disheart Apr 13 '22 at 12:15
  • @Disheart: Yes you can map one-to-one relationship. Yes, you can map it to a jOOQ generated record. I'll be happy to help you also with that third issue. Please, can you ask 3 separate questions here on stack overflow? It won't be possible to give you good answers in the comments section. I see you've already asked the first one here: https://stackoverflow.com/q/71858034/521799 – Lukas Eder Apr 14 '22 at 07:44