CODEWITHSUNDEEP
sqljooq
Ynv
Ynv

Reputation: 1974

Implicit joins and N:N tables in jOOQ

In current jOOQ versions there is a feature to implicitly join tables.

It allows one to 'navigate' using foreign key constraints defined on tables.

Does it also work on n:n tables? For example if I have DOCUMENT, and CLIENT, and a n:n table between them CLIENT_DOCUMENT, how would I translate the following query to use the new syntax?

  db.select(*DOCUMENT.fields())
            .from(DOCUMENT)
            .join(CLIENT_DOCUMENT).onKey()
            .join(CLIENT).onKey()
            .where(CLIENT.CLIENT_NAME.eq(name))
            .fetchInto(Document::class.java)

Thanks.

Upvotes: 1

Views: 298

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221165

The implicit join feature, as of jOOQ 3.13, only works for to-one relationships, not for to-many relationships. There are a variety of planned improvements for future versions: #7536.

However, a m:n relationship can be seen as two to-one relationships starting from the relationship table. You could write:

db.select(CLIENT_DOCUMENT.document().fields())
  .from(CLIENT_DOCUMENT)
  .where(CLIENT_DOCUMENT.client().CLIENT_NAME.eq(name))
  .fetchInto(Document::class.java)

Upvotes: 1

Related Questions