Mapping joined tables of same type in JOOQ

Question

I have the following query:

sql
  .select()
  .from(ITEMS.join(ITEMSOWNERS).on(ITEMSOWNERS.ITEM_ID.eq(ITEMS.ID)))
             .join(USERS).on(ITEMSOWNERS.OWNER_ID.eq(USERS.ID))
             .leftJoin(ITEMSREVIEWERS).on(ITEMSREVIEWERS.ITEM_ID.eq(ITEMS.ID))
             .join(USERS).on(ITEMSREVIEWERS.REVIEWER_ID.eq(USERS.ID))
  .where(PUBLIC_ID.eq(publicId))
  .fetchGroups(WALLETS)

I'd like to get those back in some kind of tuple form, taking into account that there are two types of USERS results here, i.e. I have a structure of the kind:

- ITEMS (given the `WHERE` clause there should be only one of those at most, though)
  - OWNERS
  - REVIEWERS

I think I know that I could use fetchGroups to get from Record to a Map<Item, Record>, but I am not quite sure how to proceed further in regards to the resulting Record which should contain User twice, i.e.

innerRecord.into(USERS) // this should work okay, I guess
innerRecord.into(USERS) // how to make this happen on the "second" set of User columns?

Ideally I'd like to map the second User into an Optional since it may not be around.

Answer 1

You need to alias your USERS tables. This is generally a good idea in SQL (regardless if you're using jOOQ), if you're joining the same table twice:

val owners = USERS.as("owners");
val reviewers = USERS.as("reviewers");

sql
  .select()
  .from(ITEMS.join(ITEMSOWNERS).on(ITEMSOWNERS.ITEM_ID.eq(ITEMS.ID)))
             .join(owners).on(ITEMSOWNERS.OWNER_ID.eq(owners.ID))
             .leftJoin(ITEMSREVIEWERS).on(ITEMSREVIEWERS.ITEM_ID.eq(ITEMS.ID))
             .join(reviewers).on(ITEMSREVIEWERS.REVIEWER_ID.eq(reviewers.ID))
  .where(PUBLIC_ID.eq(publicId))
  .fetchGroups(WALLETS)

You can then access the two table's columns explicitly as such:

innerRecord.into(owners)
innerRecord.into(reviewers)

Ideally I'd like to map the second User into an Optional since it may not be around.

There's no such method in jOOQ. Wrap it explicitly as follows:

Optional.of(innerRecord.into(reviewers))

A note on SQL correctness

I don't think your SQL query is correct. You should either left join your reviewers table to the join graph:

  .from(ITEMS.join(ITEMSOWNERS).on(ITEMSOWNERS.ITEM_ID.eq(ITEMS.ID)))
             .join(owners).on(ITEMSOWNERS.OWNER_ID.eq(owners.ID))
             .leftJoin(ITEMSREVIEWERS).on(ITEMSREVIEWERS.ITEM_ID.eq(ITEMS.ID))
             .leftJoin(reviewers).on(ITEMSREVIEWERS.REVIEWER_ID.eq(reviewers.ID))

... or nest that join as follows:

  .from(ITEMS.join(ITEMSOWNERS).on(ITEMSOWNERS.ITEM_ID.eq(ITEMS.ID)))
             .join(owners).on(ITEMSOWNERS.OWNER_ID.eq(owners.ID))
             .leftJoin(ITEMSREVIEWERS
                 .join(reviewers).on(ITEMSREVIEWERS.REVIEWER_ID.eq(reviewers.ID))
             ).on(ITEMSREVIEWERS.ITEM_ID.eq(ITEMS.ID))