I'm following a jOOQ blog post about paging metadata, and trying to implement it generally for a project. It demonstrates how to generically take an existing Select<?> and wrap it up to contain information about the current page, and how many other pages exist:
public static Select<?> paginate( DSLContext ctx, Select<?> original, Field<?>[] sort, int limit, int offset ) { Table<?> u = original.asTable("u"); Field<Integer> totalRows = count().over().as("total_rows"); Field<Integer> row = rowNumber().over().orderBy(u.fields(sort)) .as("row"); Table<?> t = ctx .select(u.asterisk()) .select(totalRows, row) .from(u) .orderBy(u.fields(sort)) .limit(limit) .offset(offset) .asTable("t"); Select<?> result = ctx .select(t.fields(original.getSelect().toArray(Field[]::new))) .select( count().over().as("actual_page_size"), field(max(t.field(row)).over().eq(t.field(totalRows))) .as("last_page"), t.field(totalRows), t.field(row), t.field(row).minus(inline(1)).div(limit).plus(inline(1)) .as("current_page")) .from(t) .orderBy(t.fields(sort)); // System.out.println(result); return result; }And here’s how you call the utility:
java System.out.println( paginate( ctx, ctx.select(ACTOR.ACTOR_ID, ACTOR.FIRST_NAME, ACTOR.LAST_NAME) .from(ACTOR), new Field[] { ACTOR.ACTOR_ID }, 15, 30 ).fetch() );Notice that you can plug in arbitrary SQL fragments into that utility and paginate them. Irrespective of the complexity (including joins, other window functions, grouping, recursion, and what not), jOOQ will have you covered and will now paginate things for you.
This works nicely for simple cases, but I am finding that it fails in any case where two columns happen to have the same name across joins - normally, the record.into(FooRecord.class) and record.into(BarRecord.class) would extract those columns and make them separately available to the caller.
For example, given two tables, foo and bar, where both have an id column, and bar has a foo_id foreign key. Then, I can write:
var select = ctx
.select(FOO.fields())
.select(BAR.fields())
.from(BAR)
.leftJoin(FOO).on(BAR.FOO_ID.eq(FOO.ID));
// Render the SQL
System.out.println(select);
var data = select.fetch();
// Show the full query's results, then the fields that came from FOO and BAR
System.out.println(data);
System.out.println(data.get(2).into(FOO));
System.out.println(data.get(2).into(BAR));
Results:
select "myschema"."foo"."id", "myschema"."bar"."id", "myschema"."bar"."foo_id"
from "myschema"."bar"
left outer join "myschema"."foo"
on "myschema"."bar"."foo_id" = "myschema"."foo"."id"
+----+----+------+
| id| id|foo_id|
+----+----+------+
| 1| 1| 1|
| 2| 2| 2|
| 2| 3| 2|
+----+----+------+
+----+
| id|
+----+
| 2|
+----+
+----+------+
| id|foo_id|
+----+------+
| 3| 2|
+----+------+
Both jOOQ and the database (Postgres in this case) are happy with this, despite having more than one column with the same name.
Next, I try to pass this Select<?> to the paginate() method above:
var pagedSelect = paginate(ctx, original, new Field[] { FOO.ID }, 10, 0);
System.out.println(pagedSelect);
var pagedData = pagedSelect.fetch();//error
select
"t"."id",
"t"."id",
"t"."foo_id",
count(*) over () as "actual_page_size",
(max("t"."row") over () = "t"."total_rows") as "last_page",
"t"."total_rows",
"t"."row",
((("t"."row" - 1) / 10) + 1) as "current_page"
from (
select
"u".*,
count(*) over () as "total_rows",
row_number() over (order by "u"."id") as "row"
from (
select "myschema"."foo"."id", "myschema"."bar"."id", "myschema"."bar"."foo_id"
from "myschema"."bar"
left outer join "myschema"."foo"
on "myschema"."bar"."foo_id" = "myschema"."foo"."id"
) as "u"
order by "u"."id"
offset 0 rows
fetch next 10 rows only
) as "t"
order by "t"."id"
ERROR: column referenced "id" is ambiguous
My naive approach was to first try to modify paginate(...) to make the outer-most SELECT reference the fully qualified fields, table and all, but since we're querying from an anonymous table, that doesn't work, same error (though for a different reason, since now jOOQ is generating an alias for original.
Next I considered trying to visit original and create a copy of it, using AS to rename columns as f1, f2, f3, but I couldn't find a straightforward way to do this rewrite. Even if this had worked, it would make it hard to map them back again from the caller, which would need to then know the order of all the .select()ed fields to be able to find them again - and since the original might have itself been built from other dynamic SQL operations, that might not be feasible.
Finally I considered an approach where every column of every table was already qualified with the table name already, but beyond this being onerous, it also doesn't help at all in cases where a table could be joined twice into the same query.
What's the correct dynamic SQL approach with jOOQ to make it possible to wrap a query with pagination, and still map records back to their individual tables?
--
EDIT: Based on the comments, I've modified the query from the blog post, and while this actually emits runnable SQL, the premise of the blog post still seems faulty, and still eludes me.
Irrespective of the complexity (including joins, other window functions, grouping, recursion, and what not), jOOQ will have you covered and will now paginate things for you.
In this iteration, the as syntax for the nested selects now come with renamed columns, and the outer query uses those column names. In an attempt to still permit Record.into(Table) to work, it maps back again to the original columns, in order:
public static Select<?> paginate(
DSLContext ctx,
Select<?> original,
Field<?>[] sort,
int limit,
int offset ) {
Map<List<String>, String> aliases = new LinkedHashMap<>();
List<Field<?>> reverseAlias = new ArrayList<>();
List<Field<?>> select = original.getSelect();
for (int i = 0; i < select.size(); i++) {
Field<?> field = select.get(i);
aliases.put(Arrays.asList(field.getQualifiedName().getName()), "c" + i);
}
Table<?> u = original.asTable("u", aliases.values().toArray(String[]::new));
Field<Integer> totalRows = count().over().as("total_rows");
String[] sortFieldNames = Arrays.stream(sort).map(o -> aliases.get(Arrays.asList(o.getQualifiedName().getName()))).toArray(String[]::new);
Field<Integer> row = rowNumber().over().orderBy(u.fields(sortFieldNames))
.as("row");
Table<?> t = ctx.select(u.asterisk())
.select(totalRows, row)
.from(u)
.orderBy(u.fields(sortFieldNames))
.limit(limit)
.offset(offset)
.asTable("t");
for (int i = 0; i < select.size(); i++) {
Field<?> field = select.get(i);
reverseAlias.add(t.field("c" + i).as(field.getName()));
}
Select<?> result = ctx
.select(reverseAlias)
.select(
count().over().as("actual_page_size"),
field(max(t.field(row)).over().eq(t.field(totalRows)))
.as("last_page"),
t.field(totalRows),
t.field(row),
t.field(row).minus(inline(1)).div(limit).plus(inline(1))
.as("current_page"))
.from(t)
.orderBy(t.fields(sortFieldNames));
return result;
}
This generates the following SQL:
select
"t"."c1" as "id",
"t"."c2" as "id",
"t"."c3" as "foo_id",
count(*) over () as "actual_page_size",
(max("t"."row") over () = "t"."total_rows") as "last_page",
"t"."total_rows",
"t"."row",
((("t"."row" - 1) / 10) + 1) as "current_page"
from (
select
"u".*,
count(*) over () as "total_rows",
row_number() over (order by "u"."c1") as "row"
from (
select "myschema"."foo"."id", "myschema"."bar"."id", "myschema"."bar"."foo_id"
from "myschema"."bar"
left outer join "myschema"."foo"
on "myschema"."bar"."foo_id" = "myschema"."foo"."id"
) as "u" ("c1", "c2", "c3")
order by "u"."c1"
offset 0 rows
fetch next 10 rows only
) as "t"
order by "t"."c1"
Note that as in the initial result sets, the first two columns are still labeled as id. However, Record.into(Table) no longer has the context it needs to understand that the first id column is for the foo table, and the second is for the bar table:
+----+----+------+----------------+---------+----------+----+------------+
| id| id|foo_id|actual_page_size|last_page|total_rows| row|current_page|
+----+----+------+----------------+---------+----------+----+------------+
| 1| 1| 1| 3|true | 3| 1| 1|
| 2| 2| 2| 3|true | 3| 2| 1|
| 2| 3| 2| 3|true | 3| 3| 1|
+----+----+------+----------------+---------+----------+----+------------+
+----+
| id|
+----+
| 2|
+----+
+----+------+
| id|foo_id|
+----+------+
| 2| 2|
+----+------+
So, to put the question in concrete terms: Is there a way to indicate to jOOQ that a Select<?>'s fields should be mapped to a particular Table?
And, as a more general question, but less subject to the X/Y problem: How can this paginate() method actually be used as in the example, with joins, where the joined tables have colliding column names? Is there a problem with this strategy of building up a query dynamically, and is there another strategy that can be followed instead, without this shortcoming?
