jOOQ select Master-Details filter by details's property

Question

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?

Answer 1

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).