Conditional query

[giltayar]

Is there a way to do a conditional query, i.e. have the query be different based on a condition. An example (which I'm pretty sure doesn't work):

await db.query(sql`
SELECT * FROM some_table
${someFilter != null ? sql`WHERE some_field = ${someFilter}` : ''}
`)

My only option now, given that I couldn't find a way to do this, is to have two query statements: one with the "WHERE" and one without.

[DeeLindesay]

If I've understood the docs correctly I think you could do this with sql.join

await db.query(sql.join(
  [
     sql`SELECT * FROM some_table`,
     someFilter != null ? sql` WHERE some_field = ${someFilter}` : sql``
   ],
   ""
))

[ForbesLindesay]

You just need to tag the empty string as a fragment of sql, otherwise it is treated as a parameter:

await db.query(sql`
SELECT * FROM some_table
${someFilter != null ? sql`WHERE some_field = ${someFilter}` : sql``}
`)

Using sql.join also works, you can use whichever approach you prefer.

[giltayar]

Thanks @ForbesLindesay and @DeeLindesay!

Both of your suggestions work. I prefer Dee's suggestion because it doesn't necessitate a nested template literal. But good to know that I can use both.