How do you get conditional filters within query files?

Question

I was using conditional template string interpolation before, which looked like this:

const queryArgs = {
  type,
  status,
}
const query = `
  SELECT
    id,
    type,
    status,
    content
  FROM
    some_table
  WHERE
    ${type ? "type = $(type)" : ""}
    ${status ? "AND status = $(status)" : ""}
  ORDER BY
    id
`

It did the work and didn't violate pg-promise rules in regards to template string interpolation, since it wasn't interpolating the input values but rather their args references.
Is there a way to get the similar logic for query files? A lot of filters like this tend to be compounded, so writing every single combo of filter groups is not an option, especially since these queries are going to be used as a part of CTE chain.

If I understood the problem correctly, you can use `CASE` (https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-case/). The shared link doesn't give an example of using it in `WHERE` but you'll find some online. — long_hair_programmer, Sep 14 '22 at 13:56
Use [Raw Text](https://github.com/vitaly-t/pg-promise#raw-text) filter inside your SQL file: `${type:raw}` and `${status:raw}`, and format the condition in the code and pass it in pre-formatted. — vitaly-t, Sep 14 '22 at 14:09
Does this answer your question? [Possible to add dynamic WHERE clause with a QueryFile?](https://stackoverflow.com/questions/57808220/possible-to-add-dynamic-where-clause-with-a-queryfile) — vitaly-t, Sep 16 '22 at 18:17
No, I found the way to write them purely in SQL file and interpolate values where needed so I'll post it as an answer. — Biller Builder, Oct 19 '22 at 08:24

score 0 · Accepted Answer · answered Oct 19 '22 at 08:39

It can be done with pure SQL:

SELECT
  id,
  type,
  status,
  content
FROM
  some_table
WHERE
  (${type} IS NULL OR type = ${type})
  AND
  (${status} IS NULL OR status = ${status})
ORDER BY
  id

OR and AND logic is similar to javascript || and && operators. So the expression above means "if type is not null then filter by type and if status not null filter by status". So if both values are NULL then the condition evaluates to TRUE and thus all rows are included in the result.
It might look a bit verbose at first, but the perk of this approach is these conditions can be written inside stored procedures and the filter values passed as arguments. Thus no multiple query files needed for a single operation.

How do you get conditional filters within query files?

1 Answers1