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 implicitto-manypath joins would cause. The main problem is that an implicitto-manypath placed in the SELECT clause or WHERE clause (and other clauses) would be able to generate rows, when in factSELECTonly transforms rows (likeStream.map()) andWHEREonly filters rows (likeStream.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 ofJOIN) 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();
Feedback
Do you have any feedback about this page? We'd love to hear it!