PostHole
Compose
Login
jOOQ 3.19’s new Explicit and Implicit to-many path joins
jOOQ 3.19 finally delivers on a set of features that will greatly simplify your queries further, after jOOQ 3.11 introduced implicit to-one joins: What are these features? Many ORMs (e.g. JPA, Doctrine, jOOQ 3.11 and others) support “path joins” (they may have different names for this concept). A path join is a join derived from … Continue reading jOOQ 3.19’s new Explicit and Implicit to-many path joins →
jOOQ 3.19’s new Explicit and Implicit to-many path joins
Posted on December 28, 2023December 14, 2023 by lukaseder
jOOQ 3.19 finally delivers on a set of features that will greatly simplify your queries further, after jOOQ 3.11 introduced implicit to-one joins:
What are these features?
Many ORMs (e.g. JPA, Doctrine, jOOQ 3.11 and others) support “path joins” (they may have different names for this concept). A path join is a join derived from a path where the query language allows for navigating foreign keys. E.g. in jOOQ, you can write:
ctx.select(
CUSTOMER.FIRST_NAME,
CUSTOMER.LAST_NAME,
CUSTOMER.address().city().country().NAME)
.from(CUSTOMER)
.fetch();
The generated query looks something like this:
SELECT
customer.first_name,
customer.last_name,
country.name
FROM
customer
JOIN address ON customer.address_id = address.address_id
JOIN city ON address.city_id = city.city_id
JOIN country ON city.country_id = country.country_id
Depending on your tastes, the implicit join syntax may be much more readable than the explicit one. In addition to that, it’s impossible to ever write a wrong join predicate this way (wrong columns compared, or missing columns in a composite key) because the meta data is known to jOOQ and generated correctly, every time.
Very idiomatic SQL
In fact these features are quite idiomatic in SQL, in general. Imagine a new version of the SQL standard that allows for declaring “labels” on foreign keys:
CREATE TABLE book (
author_id INT REFERENCES author
PARENT PATH LABEL author
CHILD PATH LABEL books
And now, you could reference those labels in queries:
SELECT book.title, book.author.first_name
FROM book
Or:
SELECT
author.id,
author.first_name,
author.last_name,
COUNT(*)
LEFT JOIN author.books
GROUP BY author.id
Because: why not? We can dream! In fact, ORDBMS extensions (as implemented by Oracle), implemented something similar with the REF type, but it’s never been adopted, regrettably.
But for now, let’s look at what new things jOOQ is offering.
New: Explicit path joins
As mentioned initially, one new thing in jOOQ 3.19 is support for explicit path joins. This was rarely necessary so far, because the implicit to-one join semantics is obvious, but sometimes, you may want to make the join path declaration explicit, or have control over the join type on a per-query basis, e.g. if you prefer LEFT JOIN over INNER JOIN.
Note: jOOQ already generates LEFT JOIN for nullable foreign keys.
You can explicitly join paths like this now:
ctx.select(
CUSTOMER.FIRST_NAME,
CUSTOMER.LAST_NAME,
CUSTOMER.address().city().country().NAME)
.from(CUSTOMER)
// The entire path will be left joined:
.leftJoin(CUSTOMER.address().city().country()
.fetch();
Or even more explicitly, like this:
ctx.select(
CUSTOMER.FIRST_NAME,
CUSTOMER.LAST_NAME,
CUSTOMER.address().city().country().NAME)
.from(CUSTOMER)
.leftJoin(CUSTOMER.address())
.leftJoin(CUSTOMER.address().city())
.leftJoin(CUSTOMER.address().city().country())
.fetch();
Obviously, you can also assign each path to a local variable, and use aliases and all the other jOOQ features, as always.
Note that the JOIN .. ON clause is now optional, because jOOQ already generates it for you based on the available foreign key meta data. If you require an additional join predicate on a path (which is very rarely necessary, and now, it’s finally possible), you can do so:
ctx.select(
CUSTOMER.FIRST_NAME,
CUSTOMER.LAST_NAME,
CUSTOMER.address().city().country().NAME)
.from(CUSTOMER)
.leftJoin(CUSTOMER.address().city())
// You may have your reasons to display the country only if
// the city name starts with A
.on(CUSTOMER.address().city().NAME.like("A%"))
.leftJoin(CUSTOMER.address().city().country())
.fetch();
In order to profit from this new path based join, the <implicitJoinPathTableSubtypes/> code generation flag needs to be turned on (which it is, by default).
The feature also works without the flag, but then, the ON clause will be mandatory for most join types. Turning off the flag can be useful if you want to avoid too many types being generated by jOOQ (one Path type per table).
New: to-many path joins
The main reason for introducing the above explicit path based joins are the new to-many path joins. Implicit to-many path joins are unavailable by default (via an exception thrown), because of their weird semantics within a query. For example, when finding all the films of an actor:
ctx.select(
ACTOR.FIRST_NAME,
ACTOR.LAST_NAME,
ACTOR.film().TITLE)
.from(ACTOR)
It may be tempting to write queries this way, but this would change one of the fundamental assumptions of SQL, namely that rows can be generated only in the FROM clause (or in GROUP BY, with GROUPING SETS), and they’re filtered mainly in the WHERE, HAVING, QUALIFY clauses. See an overview of SQL clauses here.
But in the above example, a projection (i.e. an expression in SELECT) is capable of generating rows by creating a cartesian product! Just by adding the FILM.TITLE column, suddenly, an ACTOR.FIRST_NAME and ACTOR.LAST_NAME will be repeated, which may or may not be what people expect.
This is a very un-SELECT-y thing to do, as if Stream.map() could generate or filter rows!
Even worse, what if you write this:
ctx.select(ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
.from(ACTOR)
.where(ACTOR.film().TITLE.like("A%"))
.fetch();
This looks as though we’re querying for actors who played in films starting with the letter A, but in fact, we’re again creating a cartesian product between ACTOR × FILM where each actor is repeated for each matching film. Since we’re no longer projecting any FILM columns, this looks like a mistake! The result may look like this:
|first_name|last_name|
|----------|---------|
|PENELOPE |GUINESS |
|PENELOPE |GUINESS |
|PENELOPE |GUINESS |
|NICK |WAHLBERG |
|NICK |WAHLBERG |
|ED |CHASE |
|ED |CHASE |
|ED |CHASE |
And if you’re not careful, then you might be tempted to remove the duplicates with DISTINCT, which just makes things worse.
So, in order to make things explicit, you have to explicitly declare the paths in the FROM clause, e.g.:
ctx.select(
ACTOR.FIRST_NAME,
ACTOR.LAST_NAME,
ACTOR.film().TITLE)
.from(ACTOR)
.leftJoin(ACTOR.film())
.fetch();
Now, the cartesian product is visible in the jOOQ query, and doesn’t surprise you as a developer (or reviewer) of this code anymore. Plus, with to-many path joins, the INNER or OUTER semantics of the JOIN is more important than with to-one path joins, so you’re forced to make a choice.
Overriding the default
[...]