JOOQ - nested query

java, jooq, sql

Solution

Your query will look something like this:

// This is how you can select REGION.*
dsl.select(REGION.fields())

// This is how you can add more fields to the SELECT clause
   .select(

// Nested selects are just select() as well (from DSL)
        select(count())
       .from(CITY)
       .where(CITY.REGION_ID.eq(REGION.ID))

// This is how you convert an org.jooq.Select into a Field
       .asField("num")
   )
   .from(REGION)
   .fetch();

The above implementation is assuming static imports:

import static org.jooq.impl.DSL.*;
import static path.to.generated.Tables.*;

Problem

I'm trying to write this query with jooq ``` SELECT region.*, (SELECT COUNT(*) FROM city WHERE city.region_id = region.id) AS num FROM region; ``` I tried few things, but no success. So far I got nothing more than ``` dsl.select().from(Tables.REGION).fetch(); ``` How can I add the `num` column to my result? Any help appreciated.

Original source