jOOQ - Return tuple of fields from "then" in "when" query

Question

I use jOOQ with PostgreSQL and I'm trying to create a select query with multiple counts using CASE WHEN. Because it's important to count only unique records, I also use DISTINCT statement. Sometimes I need to distinct on multiple columns, which is perfectly correct in SQL syntax, but I can't figure out how to do it in jOOQ.

I use CASE for counting because I also have queries with more conditions. I simplified these examples for convenience.

Example working SQL:

select
   count(distinct (case when "UserID" = 11 then "CategoryID" end)) as "UserCatNum",
   count(distinct (case when "UserID" = 11 then ("CategoryID", "PageID") end)) as "UserPageNum"
from data_table

When I try with just one field, it works:

query.addSelect(
    DSL.countDistinct(
        DSL.when(DSL.condition(Operator.AND, conditions), CATEGORY_ID)
    ).as("UserCatNum")
);

But when I try something like this:

query.addSelect(
    DSL.countDistinct(
        DSL.when(DSL.condition(Operator.AND, conditions),
        DSL.row(CATEGORY_ID, PAGE_ID))
    ).as("UserPageNum")
);

I'm getting the following error:

Cannot interpret argument of type class org.jooq.impl.RowImpl as a Field: ("data_table"."CategoryID", "data_table"."PageID")

I tried DSL.row() because I saw a similar usage with IN statements. I also tried replacing this with DSL.select(), but it doesn't work, neither Arrays and Lists.

For now I resolved this by writing a small function:

private Field<Object> tupleField(Field... fields) {
    String tuple = Arrays.stream(fields)
        .map(f -> f.getQualifiedName().quotedName().toString())
        .collect(Collectors.joining(","));

    return DSL.field("(" + tuple + ")");
}

But it would be nice to know how to do this using jOOQ's API.

Answer 1

A row value expression (e.g. org.jooq.Row2) can be converted to an ordinary column expression (org.jooq.Field) by using the experimental DSL.rowField() methods.

This will then work in the API, and might generate the appropriate expected SQL, but as of jOOQ 3.11 / 3.12, this support is still quite experimental and not well tested. It might well be that row fields, when used in expressions like the CASE expression, will not be properly fetched from the database.