Available in versions: Dev (3.20) | Latest (3.19)

This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.

Implicit to-many path JOIN

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Support for to-many paths (implicit or explicit) has been added in 3.19. While explicit to-many paths are very powerful, users may want the convenience of the implicit to-many paths just like the implicit to-one paths. However, jOOQ doesn't support these out of the box like other ORMs might do, and as users might expect in case of simple examples. Take the following "obvious" example, for instance:

// Get all authors and count their books
create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count(AUTHOR.book().ID))
      .from(AUTHOR)
      .groupBy(AUTHOR.ID)
      .fetch();

It reads nicely: "Get all authors and count their books.". The expected query produced by this is:

SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(BOOK.ID)
FROM AUTHOR
LEFT JOIN BOOK ON BOOK.AUTHOR_ID = AUTHOR.ID
GROUP BY AUTHOR.ID

Another cool example is this clever ANTI JOIN:

// Get all authors without any book
create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
      .from(AUTHOR)
      .where(AUTHOR.book().ID.isNull())
      .fetch();
SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
FROM AUTHOR
LEFT JOIN BOOK
  ON BOOK.AUTHOR_ID = AUTHOR.ID
WHERE BOOK.ID IS NULL

But if the above is possible, then the following counter example would produce very surprising results! One would expect the inverse of an ANTI JOIN to produce a SEMI JOIN, but that wouldn't be what happens:

// Get all authors with books
create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
      .from(AUTHOR)
      .where(AUTHOR.book().ID.isNotNull())
      .fetch();
SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
FROM AUTHOR
LEFT JOIN BOOK
  ON BOOK.AUTHOR_ID = AUTHOR.ID
WHERE BOOK.ID IS NOT NULL

Now, we get duplicate authors. One per book they've written, due to the cartesian product created by the LEFT JOIN.

Accidental duplicate objects isn't the main problem that such implicit to-many path joins would cause. The main problem is that an implicit to-many path placed in the SELECT clause or WHERE clause (and other clauses) would be able to generate rows, when in fact SELECT only transforms rows (like Stream.map()) and WHERE only filters rows (like Stream.filter()). It would be very SQL-unidiomatic and confusing for these clauses to be able to effectively produce rows.

To prevent this, the default behaviour when encountering an implicit to-many join path expression an exception that is thrown.

To prevent this, users have 2 options:

  • Override the default with Settings.renderImplicitJoinToManyType. This applies to all queries and removes the above scalar subquery protection for power users who know what they're doing.
  • Use explicit path joins to specify that indeed, a LEFT JOIN (or any other type of JOIN) is indeed desired, see example below:
// Get all authors with books
create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
      .from(AUTHOR)
      .leftJoin(AUTHOR.book()) // Now, the LEFT JOIN is explicit and cartesian products aren't accidental.
      .where(AUTHOR.book().ID.isNotNull())
      .fetch();

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo