JOOQ: Dynamic join conditions

Question

I would like to create conditions from this select in JOOQ. Because in my case I want to declare a dynamic query and check if TABLE_C.FIELDC contains "foo" only when I need...

Example:

create.select().from(TABLE_A).join(TABLE_B).onKey(Keys.FK_TABLEA_TABLEB)
                    .join(TABLE_C).onKey(Keys.FK_TABLEB_TABLEC)
                    .where(TABLE_C.FIELDC.containsIgnoreCase("foo");

to:

SelectFinalStep select = create.select().from(TABLEA);

if (isFooSearched) { 
   query.addCondition( <JOIN> and <CONTAINS> like first example) 
}

How can I do this?

Answer 1

There are several ways to solve this:

Using implicit joins

In relatively simple cases, when the optional join follows a to-one relationship, you may be able to use an implicit join (if you're using the code generator):

create.select()
      .from(TABLE_A)
      .join(TABLE_B).onKey(Keys.FK_TABLEA_TABLEB)
      .where(isFooSearched
        ? TABLE_B.tableC().FIELDC.containsIgnoreCase("foo")
        : noCondition())
      .fetch();

Using SEMI JOIN instead of INNER JOIN, which makes dynamic SQL much easier

create.select()
      .from(TABLE_A)
      .where(
          isFooSearched
        ? TABLE_A.TABLE_B_ID.in(
              select(TABLE_B.ID)
             .from(TABLE_B)
             .join(TABLE_C).onKey(FK_TABLEB_TABLEC)
             .where(TABLE_C.FIELDC.containsIgnoreCase("foo"))
          )
        : trueCondition())
      .fetch();

Note that a semi join is also more formally correct in this case than an inner join, as you will not get any duplicate rows on TABLE_A for any matches in to-many relationships (removing them with DISTINCT might be wrong and certainly is inefficient).

Side-note: Not all databases recognise semi-joins in EXISTS or IN syntax, and may thus not optimally run this statement, compared to a JOIN based solution.

Using INNER JOIN as you asked for

// I'm assuming DISTINCT is required here, as you
// - are not interested in TABLE_B and TABLE_C results (semi join semantics)
// - do not want any duplicate TABLE_A values
create.selectDistinct(TABLE_A.fields())
      .from(
          isFooSearched
        ? TABLE_A
             .join(TABLE_B).onKey(FK_TABLEA_TABLEB)
             .join(TABLE_C).onKey(FK_TABLEB_TABLEC)
          )
        : TABLE_A)
      .where(
          isFooSearched
        ? TABLE_C.FIELDC.containsIgnoreCase("foo")
        : trueCondition())
      .fetch();

I've mad a few assumptions here, including the fact that DISTINCT usage could be correct on your joined variant of the query, but it is hurting you (probably) on your "default" query variant, so perhaps, shoe horning this into a single dynamic query might be overkill.

Thus...

Using two different queries

For my taste, the two queries are simple enough to allow for some duplication and simply run two different queries depending on the flag:

if (isFooSearched)
    create.select().from(TABLE_A) /* joins or semi joins here */ .fetch();
else
    create.select().from(TABLE_A).fetch();

Side note

All solutions are assuming you have these static imports in your code:

import static org.jooq.impl.DSL.*;