jOOQ - join with nested subquery

Question

Let's say I want to find out who wrote CLRS in a book db (tables BOOK, AUTHOR with a junction table BOOK_AUTHOR).

SelectConditionStep<Record1<String>> query = create
        .select(AUTHOR.LASTNAME.as("AuthorName"))
            .from(
                    (
                            BOOK.leftOuterJoin(BOOK_AUTHOR).on(BOOK.ID.eq(BOOK_AUTHOR.BOOKID))
                    ).leftOuterJoin(AUTHOR).on(AUTHOR.ID.eq(BOOK_AUTHOR.AUTHORID))
            )
            .where(BOOK.TITLE.eq(CLRS_title))
        ;

A bit inefficient to match the entire table, just to select a single book. I now want to select that book prior to the match.

The jOOQ doc on this matter led me to believe that could look something like this:

Table<Record1<Integer>> clrs = create
        .select(BOOK.ID.as("bookID"))
            .from(BOOK)
            .where(BOOK.TITLE.eq(CLRS_title))

        .asTable()
        ;

SelectJoinStep<Record1<String>> query = create
        .select(AUTHOR.LASTNAME.as("AuthorName"))
            .from(
                    (
                            clrs.leftOuterJoin(BOOK_AUTHOR).on(clrs.field("bookID").eq(BOOK_AUTHOR.BOOKID))
                    ).leftOuterJoin(AUTHOR).on(AUTHOR.ID.eq(BOOK_AUTHOR.AUTHORID))
            )
        ;

However, that fails to compile because

Cannot resolve method 'eq(org.jooq.TableField<ch.cypherk.bookdb.public_.tables.records.BookAuthorRecord,java.lang.Integer>)'

in the join condition.

What's the correct way to write this join?

Answer 1

The problem you're having

You're dereferencing a column from your derived table using Table.field(String):

clrs.field("bookID")

The type you're getting back is Field<?>, with a wildcard. Like with any generic type, once you have a wild card, a lot of operations (but not all) will no longer be possible on that type. Take List<?>, for example. You can still call List<?>.get() to retrieve an Object, but not List<?>.add(? element). In Field<?>, you can no longer call eq(), unless you cast the argument to a raw type.

You can also coerce your field's <T> type to the type you already know, e.g. by using Table.field(String, DataType<T>)

clrs.field("bookID", BOOK.ID.getDataType())

Study your various options and you might discover the one(s) you might find most useful

A better solution to your query

You don't really need to

1. Assign your subquery to a local variable
2. Use a derived table for your problem

Often with jOOQ, if you're having issues with derived tables as above, ask yourself is there really not an easier query I could write instead?

What you really need here is a semi join. Write:

// Assuming this static import
import static org.jooq.impl.DSL.*;

ctx.select(AUTHOR.LASTNAME)
   .from(AUTHOR)
   .where(AUTHOR.ID.in(
        select(BOOK_AUTHOR.AUTHORID)
       .from(BOOK_AUTHOR)
       .join(BOOK).on(BOOK.ID.eq(BOOK_AUTHOR.BOOKID))
       .where(BOOK.TITLE.eq(clrsTitle))
   )
   .fetch();