How to us CASE WHEN aggregation with bookshelf-CodePudding

I'm working with PostgreSQL and bookshelf and trying to run a simple SQL query in order to get multiple counts in a single query. This query look like:

SELECT SUM(CASE WHEN date_last_check > (now() - interval '1 MONTH') THEN 1 ELSE 0 END) as since_two_months,
       SUM(CASE WHEN date_last_check > (now() - interval '7 DAY') THEN 1 ELSE 0 END) as since_one_week,
       SUM(CASE WHEN date_last_check > (now() - interval '1 DAY') THEN 1 ELSE 0 END) as since_one_days
FROM myTable;

It seems impossible to do a CASE statement in a sum() function in bookshelf. I'm tried:

return myTable.query(function(qb:any){
  qb.sum("(CASE WHEN date_last_check > (now() - interval '1 MONTH') THEN 1 ELSE 0 END) as since_two_months")
})

And this returns the following query:

select sum("(SUM(CASE WHEN date_last_check > (now() - interval '1 MONTH') THEN 1 ELSE 0 END)") as "since_two_months" from "myTable"

This does not work because of the quotes after the sum(").

Does anyone know how to make this work without using a raw query?

CodePudding user response：

I found a poor solution, it's to use knew raw inside the bookshelf query : return myTable.query(function(qb:any){ qb.select(bookshelf.knex.raw("SUM(CASE WHEN date_last_check > (now() - interval '1 MONTH') THEN 1 ELSE 0 END) as since_one_month")); })

CodePudding user response：

Rather use modern syntax for conditional aggregates: the aggregate FILTER clause:

SELECT count(*) FILTER (WHERE date_last_check > now() - interval '1 month') AS since_two_months  -- one_month?
     , count(*) FILTER (WHERE date_last_check > now() - interval '7 days')  AS since_one_week
     , count(*) FILTER (WHERE date_last_check > now() - interval '1 day')   AS since_one_day
FROM   mytable;

See:

Aggregate columns with additional (distinct) filters