Translate the following query in spring boot jooq query builder

Question

I have the following query:

SELECT COALESCE(t2.name, t3.name) as name FROM table1 as t1
  LEFT JOIN table2 as t2 ON t1.id = t2.id
  LEFT JOIN table2 as t3 ON t1.id2 = t3.id2

So I want to translate the above into jooq query builder! I did the following, but not sure if that is right or not:

dbReader.select(
t.name
).from(table1).leftJoin(table2).on(table2.id.eq(table1.id).or(table2.id2.eq(table1.id2))

something like that... Is that correct? Or there is a better way !

Answer 1

I'm assuming you're using the code generator. In that case, the translation should be straightforward. Just declare aliased tables up front:

Table1 t1 = TABLE1.as("t1");
Table2 t2 = TABLE2.as("t2");
Table2 t3 = TABLE2.as("t3");

ctx.select(coalesce(t2.name, t3.name).as("name"))
   .from(t1)
   .leftJoin(t2).on(t1.id.eq(t2.id))
   .leftJoin(t3).on(t1.id2.eq(t3.id2))
   .fetch();

This is assuming the usual static imports:

import static org.jooq.impl.DSL.*;
import static com.example.generated.Tables.*;