将一对多关系映射到jOOQ中的记录列表

Question

我使用的是MySQL 5.7，我想将具有一对多关系的查询映射到其代表性记录的列表。

MySQL不支持多集，JSON仿真也不起作用，因为它无法在两个层次上引用别名。

我想知道是否有另一种方法可以让我尝试在一个查询中映射多个关系。

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;

Answer 1

如果不打算进一步嵌套，您仍然可以使用MULTISET\u AGG，如下所示：

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));

请记住，如果一个组织没有任何联系人，则MULTISET\u AGG生成的不是空列表。