Named and typesafe correlated subquery

Question

I'm using JOOQ v3.14.3 and trying to do a simple select with a correlated subquery. In abstract ways:

Field<?> subquery = DSL.select(MY_TABLE.TIMESTAMP_FIELD)
        .from(MY_TABLE)
        .where(...)
        .asField("timestamp");

var mainQuery = create.select(OTHER_TABLE.SOME_FIELD, subquery)....;

My issue here is, that the subquery is guaranteed to return OffsetDateTime values, but in the resulting records I can only access those values using the plain String "timestamp" and have to do the manual casting like in

OffsetDateTime timestamp = (OffsetDateTime) record.get("timestamp");

One can create a type-safe Field like this:

Field<OffsetDateTime> subquery = DSL.field(DSL.select(MY_TABLE.TIMESTAMP_FIELD)
        .from(MY_TABLE)
        .where(...));

But this field doesn't seem to have a name so I can not easily retrieve it from the resulting Record. My subquery isn't constant so I have to recreate it everytime and can not simply keep a reference to this Field.

I'd imagine something like this: Define a custom named field, then use it in subquery and in Record access

public static final Field<OffsetDateTime> CUSTOM_TIMESTAMP_FIELD = Whatever.customField("timestamp", OffsetDateTime.class);

// ...

Field<OffsetDateTime> subquery = DSL.select(MY_TABLE.TIMESTAMP_FIELD)
        .from(MY_TABLE)
        .where(...)
        .asField(CUSTOM_TIMESTAMP_FIELD);

// ...

OffsetDateTime timestamp = record.get(CUSTOM_TIMESTAMP_FIELD);

What whould be the best/nicest way to solve this?

Answer 1

Passing around the subquery reference

But this field doesn't seem to have a name so I can not easily retrieve it from the resulting Record.

Have you tried it? Because you can just use the subquery reference to retrieve the value from the record:

OffsetDateTime value = record.get(subquery)

Of course, you'd probably rather be aliasing your subquery like this:

Field<OffsetDateTime> subquery = DSL.field(DSL.select(MY_TABLE.TIMESTAMP_FIELD)
        .from(MY_TABLE)
        .where(...))
        .as("subquery");

But then, you'd still use that reference to fetch the desired value.

Using a global variable for the subquery name

The approach you were looking for can be achieved like this:

public static final Field<OffsetDateTime> CUSTOM_TIMESTAMP_FIELD = 
    DSL.field(DSL.name("timestamp"), OffsetDateTime.class);

You can now rename your subquery to it:

Field<OffsetDateTime> subquery = DSL.field(DSL.select(MY_TABLE.TIMESTAMP_FIELD)
        .from(MY_TABLE)
        .where(...))
        .as(CUSTOM_TIMESTAMP_FIELD);

And then reuse that name in your record access:

OffsetDateTime value = record.get(CUSTOM_TIMESTAMP_FIELD)