3

Is there a way to query something where you hava many conditions that can be undefined (not required)

const c = {
  id?: number
  type?: string
}

const sql = `SELECT * FROM smth WHERE id=$1 AND type=$2`

query(sql , [c.id, c.type])
ZiiMakc
  • 31,187
  • 24
  • 65
  • 105

1 Answers1

2

You could use

const sql = `SELECT * FROM smth WHERE ($1::int IS NULL OR id=$1) AND ($2::text IS NULL OR type=$2)`;

but in general this is the place where query builder libraries are the appropriate solution.

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
  • hm, i get error: 'could not determine data type of parameter $1' – ZiiMakc Jul 16 '20 at 12:56
  • 1
    @RTW In that case, try with explicit type annotations – Bergi Jul 16 '20 at 12:57
  • 2
    That's correct, but be warned that it may be harder to makes such a statement work efficiently with indexes. Sometimes it will be better to have several prepared statements, one for each case. – Laurenz Albe Jul 16 '20 at 13:30
  • @LaurenzAlbe thanks, i think i will just map arguments by hand then – ZiiMakc Jul 16 '20 at 14:15
  • @LaurenzAlbe You mean the problem is when the query plan is cached between executions with different arguments, where different and more efficient plans would have been found if the `null` value of the argument was known? – Bergi Jul 16 '20 at 14:38
  • @Bergi No, I meant that it is difficult to index for `12 IS NULL OR id = 12`. Your answer is correct, I just wanted to mention the drawback of having a "universal" prepared statement. – Laurenz Albe Jul 16 '20 at 14:48
  • @LaurenzAlbe Oh, I had hoped postgres would do some constant folding before selecting an index. – Bergi Jul 16 '20 at 14:54
  • @Bergi That would happen when the statement is planned. But prepared statements tend to cache plans. I'm not saying it is impossible. – Laurenz Albe Jul 16 '20 at 15:01