JOOQ fetch over foreign keys table

I have three tables:

Users
Keys
UserKeys

The UserKeys table has both primary keys from Users and Keys tables to establish the relation between users and keys.

How to fetch a User with all it's related keys?

What if additional tables exist (for instance UserRoles), etc. In general, how to fetch a user and all associated rows, related via foreign keys tables?

Using standard SQL JOIN

I'm assuming you're using jOOQ's code generator. You write a join just like you would write a join in SQL:

ctx.select() // Optionally, list columns here, explicitly
   .from(USERS)
   .join(USER_KEYS).on(USERS.ID.eq(USER_KEYS.USER_ID))
   .join(KEYS).on(USER_KEYS.KEY_ID.eq(KEYS.ID))
   .where(USERS.NAME.eq("something"))
   .fetch();

Nesting collections

> What if additional tables exist (for instance UserRoles), etc. In general, how to fetch a user and all associated rows, related via foreign keys tables?

I'm not sure if this is still the same question. The above may have been about how to do joins in general, this one seems to be more specific about how to fetch nested collections?

Because a JOIN will always produce cartesian products, which are undesirable, once you're joining several to-many paths. Starting from jOOQ 3.14, you can use SQL/XML or SQL/JSON as a workaround for this, if your database supports that. Starting from jOOQ 3.15, you can use MULTISET. For example, the JSON solution might look like this:

List<User> users =
ctx.select(jsonObject(
     jsonEntry("id", USERS.ID),
     jsonEntry("name", USERS.NAME),
     jsonEntry("keys", field(
       select(jsonArrayAgg(jsonObject(KEYS.NAME, KEYS.ID)))
       .from(KEYS)
       .join(USER_KEYS).on(KEYS.ID.eq(USER_KEYS.KEY_ID))
       .where(USER_KEYS.USER_ID.eq(USER.ID))
     )),
     jsonEntry("roles", field(
       select(jsonArrayAgg(jsonObject(ROLES.NAME, ROLES.ID)))
       .from(ROLES)
       .join(USER_ROLES).on(ROLES.ID.eq(USER_ROLES.ROLE_ID))
       .where(USER_ROLES.USER_ID.eq(USER.ID))
     ))
   ))
   .from(USERS)
   .where(USERS.NAME.eq("something"))
   .fetchInto(User.class);

Assuming the User class looks like this, and that you have Gson or Jackson on your classpath to map from JSON to your Java data structures:

class Key {
  long id;
  String name;
}

class Role {
  long id;
  String name;
}

class User {
  long id;
  String name;

  List<Key> keys;
  List<Role> roles;
}

Of course, you don't have to map to Java data structures and produce a JSON result directly, without further mapping. See also this blog post for more details, or this one explaining how to use MULTISET.

Note that JSON_ARRAYAGG() aggregates empty sets into NULL, not into an empty []. If that's a problem, use COALESCE()

The MULTISET solution would look like this:

List<User> users =
ctx.select(
     USERS.ID,
     USERS.NAME,
     multiset(
       select(KEYS.NAME, KEYS.ID)
       .from(KEYS)
       .join(USER_KEYS).on(KEYS.ID.eq(USER_KEYS.KEY_ID))
       .where(USER_KEYS.USER_ID.eq(USER.ID))
     ).as("keys").convertFrom(r -> r.map(Records.mapping(Key::new))),
     multiset(
       select(ROLES.NAME, ROLES.ID)
       .from(ROLES)
       .join(USER_ROLES).on(ROLES.ID.eq(USER_ROLES.ROLE_ID))
       .where(USER_ROLES.USER_ID.eq(USER.ID))
     ).as("roles").convertFrom(r -> r.map(Records.mapping(Role::new)))
   )
   .from(USERS)
   .where(USERS.NAME.eq("something"))
   .fetch(Records.mapping(User::new));

The above approach using the various Records.mapping() overloads along with ad-hoc data type conversion assumes the presence of an immutable constructor, such as you'd get if your classes were Java 16 records:

record Key (int id, String name) {}
record Role (int id, String name) {}
record User (int id, String name, List<Key> keys, List<Role> roles) {}

Using multiple queries

If you cannot use the above approach, because you can't work with jOOQ 3.14 (yet), or because your RDBMS doesn't support SQL/XML or SQL/JSON, you can run several queries and assemble the results manually on your end.