Parameterizing a query with a varying amount of WHERE clause conditionals-CodePudding

Let's imagine we have a table containing columns for 'color' and for 'size'. I have a list with color-size combinations (e.g. [(red, small), (blue, medium)]), of which the length is unknown. The table should be filtered based on this list, so that the result contains only the rows where the combinations apply.

A query based on the example would look like this:

SELECT * FROM items WHERE ((color = 'red' AND size = 'small') OR (color = 'blue' and size = 'medium'));

Parameterizing this query wouldn't work of course, since the amount of combinations varies.

Is there a way to achieve this using the parameterized queries like the ones that are use in node-postgres? The only solution I can think of is using string interpolation, which doesn't appear to be a safe.

CodePudding user response：

It looks like good scenario for IN operator

select * from items where 
(color, size) in (('red','small'), ('blue','medium'))

and it can be parametrized using arrays

select * from items where 
(color, size) in (
  select unnest (array['red','blue']), unnest(array['small','medium']))

First array is for colors, second for sizes. Unnest in one select create pairs.Arrays should have the same number of elements.

And arrays can be passed as parameters to query.