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.

Answer 1

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.