I am working on a complex query that contains a common table expression and multiple subqueries. I am trying to keep the code readable by splitting it into methods but I am struggling a bit.
Is there a way to return a custom JOOQ record when building a common table expression or subquery instead of the standard JOOQ Records?
An example of a common table expression:
public static final String COLUMN1 = "column_1";
public static final String COLUMN2 = "column_2";
public static final String COLUMN3 = "column_3";
public static final String COLUMN4 = "column_4";
public static final String COLUMN5 = "column_5";
public static final String COLUMN6 = "column_6";
public CommonTableExpression<Record6<Long, String, String, LocalDate, LocalDate, Boolean>> getMyFirstCTE() {
var t = MY_TABLE.as("t");
return name("t")
.fields(COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5, COLUMN6)
.as(
select(
t.COLUMN1,
t.COLUMN2,
t.COLUMN3,
t.COLUMN4,
t.COLUMN5,
t.COLUMN6)
.from(t)
.where(t.COLUMN6.isFalse()));
}
and an example of a subquery:
public Table<Record6<Long, String, String, LocalDate, LocalDate, Boolean>> getMyFirstTable() {
var t = MY_TABLE.as("t");
return select(
t.COLUMN1,
t.COLUMN2,
t.COLUMN3,
t.COLUMN4,
t.COLUMN5,
t.COLUMN6)
.from(t)
.where(t.COLUMN6.isFalse()).asTable("t");
}
If the caller wants to use the fields from the common table expression it's expressed as shown below (same holds for the subquery):
var cte = getMyFirstCTE();
var column1 = cte.field(COLUMN1, Long.class);
var column2 = cte.field(COLUMN2, String.class);
var column3 = cte.field(COLUMN3, String.class);
var column4 = cte.field(COLUMN4, LocalDate.class);
var column5 = cte.field(COLUMN5, LocalDate.class);
var column6 = cte.field(COLUMN6, Boolean.class);
It would be nice to have these signatures instead:
public static CommonTableExpression<MyFirstRecord> getMyFirstCTE() {}
public static Table<MyFirstRecord> getMyFirstTable() {}
Not only for readability but also to (hopefully) not having to explicitly add the class type and be able to do something like MyFirstRecord.COLUMN1.
Is there a way to do this?