jOOQ select Master-Details filter by details's property

Currently I am using the latest jOOQ 3.19.5(R2dbc/Postgres) in my project.

I encountered an issue like this.

Given master and details` table.

|master |
id, 
type 

|details| 
id, 
master_id, 
other_id // connect other tables.

I want to query master and details together with the following condition from external parameters:

• master type
• details other_id

I tried to use the following query clause:

dslContext
  .select(
  master.id, 
  ...other master fields, 
  multiple(
    select(details.id, details.other_id)
      .from(details)
      .where(details.master_id.eq(masters.id).and(details.other_id.eq(other_id_param))
  )
)
  .from(master)
  .where(master.type.eq(type_param))

But this query will includes all masters that type is type_param with some empty details.

I try to add a count subselect clause as field to filter out result like this.

dslContext
  .select(
  master.id, 
  ...other master fields, 
  multiple(
    select(details.id, details.other_id)
      .from(details)
      .where(details.master_id.eq(masters.id).and(details.other_id.eq(other_id_param))
   ),
  select(field("count(*)", BIGINT))
      .from(details)
      .where(details.master_id.eq(masters.id).and(details.other_id.eq(other_id_param))
     .asField<Long>("details_count")
)
  .from(master)
  .where(master.type.eq(type_param).and(field("details_count").greaterThat(0)))

It doesn't work, and report error in the generated SQL:

 column "details_count" does not exist

When I added an exists to where, it works.

dslContext
  .select(
    master.id, 
    ...other master fields, 
    multiple(
      select(details.id, details.other_id)
        .from(details)
        .where(details.master_id.eq(masters.id)
          .and(details.other_id.eq(other_id_param))
     )
  )
  .from(master)
  .where(master.type.eq(type_param)
    .andExists(  
      selectOne()
        .from(details)      
        .where(details.master_id.eq(masters.id)
         .and(details.other_id.eq(other_id_param))
      )

Is there a better SQL to select the master/details in one query and filter by the detail's property?

You cannot filter anything using WHERE that you've projected in the SELECT clause due to the logical order of operations in SQL.

In order to make your COUNT(*) value available to your WHERE clause, you have to push it into the FROM clause, e.g. by using a derived table, or by using LATERAL - in your particular case it would work. I've described this technique also here, where LATERAL is used to create "local column variables" in SQL

E.g. this could work:

// Create a derived table
val t = 
  select(count())
  .from(details)
  .where(details.master_id.eq(master.id))
  .and(details.other_id.eq(other_id_param))
  .asTable("t", "c")

// Dereference the count column from it
val c = t.field("c", INTEGER);

dslContext
  .select(
    master.id, 
    // ...other master fields, 
    multiset(
      select(details.id, details.other_id)
      .from(details)
      .where(details.master_id.eq(master.id))
      .and(details.other_id.eq(other_id_param))),

    // Project the count
    c
  )
  .from(master)

  // Lateral join it
  .crossJoin(lateral(t))

  // Reference the count in WHERE
  .where(master.type.eq(type_param).and(c.gt(0)))

Note though that your EXISTS approach will certainly outperform any COUNT(*) > 0 approach as also recommended by jOOQ (if you don't really need to project the exact count value).