jOOQ 3.19 offers many new and useful path based join features

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(*)
FROM author
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)
   .fetch();

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

Note that if you disagree with the above default of disallowing such queries, you can tell jOOQ to allow implicit to-many path joins by specifying a new Settings.renderImplicitJoinType value:

Settings settings = new Settings()
    .withRenderImplicitJoinType(RenderImplicitJoinType.LEFT_JOIN);

Many-to-many paths

You may have noticed in the examples above that we skipped the relationship table when writing ACTOR.film(). This is purely a code generation feature, where the code generator recognises relationship tables based on a unique constraint on the two foreign keys:

CREATE TABLE film_actor (
  actor_id BIGINT REFERENCES actor,
  film_id BIGINT REFERENCES film,

  PRIMARY KEY (actor_id, film_id)
)

Because you love normalisation (there’s a constraint on the foreign keys) and you hate slow queries (you didn’t use an unnecessary surrogate key), this clearly qualifies as a relationship table to jOOQ.

Hence, you can write ACTOR.film().TITLE instead of ACTOR.filmActor().film().TITLE. If you ever need to access auxiliary attributes on the relationship table, you can obviously still do that, as both paths are available from the code generator.

New: implicit path correlation

Possibly the most powerful new feature is the implicit path correlation support, which allows for correlating subqueries based on paths that start with a table reference of the outer query. This is again best explained by example.

Before, you had to correlate subqueries explicitly, like this, e.g. to find all actors that played in films whose title starts with "A":

ctx.select(ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
   .from(ACTOR)
   .where(exists(
       selectOne()
       .from(FILM_ACTOR)
       .where(FILM_ACTOR.ACTOR_ID.eq(ACTOR.ACTOR_ID))
       .and(FILM_ACTOR.film().TITLE.like("A%"))
   ))
   .fetch();

This is quickly very tedious to write, and unreadable. Now, with implicit path correlations, you can just access the FILM_ACTOR and FILM tables from the ACTOR table in the correlated subquery!

ctx.select(ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
   .from(ACTOR)
   .where(exists(
       selectOne()
       .from(ACTOR.film())
       .where(ACTOR.film().TITLE.like("A%"))
   ))
   .fetch();

Or even:

ctx.select(ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
   .from(ACTOR)
   .leftSemiJoin(ACTOR.film())
   .on(ACTOR.film().TITLE.like("A%"))
   .fetch();

This is particularly useful with MULTISET correlated subqueries as well! Getting actors, and all their films, and all their film categories is a breeze, even more than before:

ctx.select(
       ACTOR.FIRST_NAME, 
       ACTOR.LAST_NAME,
       multiset(select(ACTOR.film().TITLE).from(ACTOR.film())).as("films"),
       multiset(
           selectDistinct(ACTOR.film().category().NAME)
           .from(ACTOR.film().category())
       ).as("categories")
   )
   .from(ACTOR)
   .fetch();

This is how simple it is now to produce a query generating data like this:

[
{
"first_name":"PENELOPE",
"last_name":"GUINESS",
"films":[
{ "title":"ACADEMY DINOSAUR" },
{ "title":"ANACONDA CONFESSIONS" },
{ "title":"ANGELS LIFE" },
...
],
"categories":[
{ "name":"Family" },
{ "name":"Games" },
{ "name":"Animation" },
...
]
},
{
"first_name":"NICK",
"last_name":"WAHLBERG",
...
}
]

Everything continues to be type safe, and you can continue combining this with ad-hoc conversion in order to map data to your DTOs very easily:

record Actor (
    String firstName, String lastName, 
    List<String> titles, 
    List<String> categories
) {}

List<Actor> actors =
ctx.select(
       ACTOR.FIRST_NAME, 
       ACTOR.LAST_NAME,
       multiset(select(ACTOR.film().TITLE).from(ACTOR.film())).as("films")
           .convertFrom(r -> r.collect(Records.intoList())),
       multiset(
           selectDistinct(ACTOR.film().category().NAME)
           .from(ACTOR.film().category())
       ).as("categories")
           .convertFrom(r -> r.collect(Records.intoList()))
   )
   .from(ACTOR)
   .fetch(Records.mapping(Actor::new));

It’s really hard not to love this!

Conclusion

Time to upgrade to jOOQ 3.19! Path based implicit joins have been around for many years, since jOOQ 3.11. But now, with these 3 new features, you’ll love them even more!

Get jOOQ 3.19 now!