How to use SUM inside COALESCE in JOOQ

Question

Given below is a gist of the query, which I'm able to run successfully in MySQL

SELECT a.*, 
COALESCE(SUM(condition1 or condition2), 0) as countColumn
FROM table a 
-- left joins with multiple tables
GROUP BY a.id;

Now, I'm trying to use it with JOOQ.

ctx.select(a.asterisk(),                
            coalesce(sum("How to get this ?")).as("columnCount"))
            .from(a)
            .leftJoin(b).on(someCondition)
            .leftJoin(c).on(someCondition))
            .leftJoin(d).on(someCondition)
            .leftJoin(e).on(someCondition)
            .groupBy(a.ID);     

I'm having a hard time preparing the coalesce() part, and would really appreciate some help.

Answer 1

jOOQ's API is more strict about the distinction between Condition and Field<Boolean>, which means you cannot simply treat booleans as numbers as you can in MySQL. It's usually not a bad idea to be explicit about data types to prevent edge cases, so this strictness isn't necessarly a bad thing.

So, you can transform your booleans to integers as follows:

coalesce(
  sum(
    when(condition1.or(condition2), inline(1))
    .else_(inline(0))
  ), 
  inline(0)
)

But even better than that, why not use a standard SQL FILTER clause, which can be emulated in MySQL using a COUNT(CASE ...) aggregate function:

count().filterWhere(condition1.or(condition2))