Choice in Jooq

Question

The first jooq user is here. I need to convert a regular SQL statement with nested selection below to jooq. Any idea if I'm on the right track? I appreciate any help.

//select * 
//from profile 
//where (profile_id, effective_date) in (
//                select profile_id, max(effective_date) as date 
//                from profile 
//                group by profile_id
//                )

This is what I have, but not sure what is even correct:

Result<Record> profiles = dsl_
.select(PROFILE.fields())
.from(PROFILE)
.where(PROFILE.PROFILE_ID, PROFILE.EFFECTIVE_DATE) in (create
    .select(PROFILE.PROFILE_ID, max(PROFILE.EFFECTIVE_DATE) as date
    .from(PROFILE)
    .groupBy(PROFILE.PROFILE_ID)))
    .fetch();

Answer 1

You want to use the constructor DSL.row()to build the predicate of the string expression> .

Here's how to do it with jOOQ:

// Assuming this:
import static org.jooq.impl.DSL.*;

// Write
Result<Record> profiles = dsl_
.select(PROFILE.fields())
.from(PROFILE)
.where(row(PROFILE.PROFILE_ID, PROFILE.EFFECTIVE_DATE).in(
     select(PROFILE.PROFILE_ID, max(PROFILE.EFFECTIVE_DATE).as("date"))
    .from(PROFILE)
    .groupBy(PROFILE.PROFILE_ID)
))
.fetch();