postgREST - Select query from two columns with one IN condition

Question

I have a postgesql table test_1 as follows :

|| ID ||         container_id       ||               product_id            ||
-----------------------------------------------------------------------------
||  1 ||           1                ||                   1                 ||
-----------------------------------------------------------------------------
||  2 ||           1                ||                   2                 ||
-----------------------------------------------------------------------------
||  3 ||           2                ||                   1                 ||
-----------------------------------------------------------------------------
||  4 ||           2                ||                   2                 ||
-----------------------------------------------------------------------------

I need to select the query :

select * from test_1 where (container_id,product_id) in ((1,1),(1,2),(2,1));

This should return all rows with ids from 1 to 3.

Is there a postgREST operator that can select the needed query?

My url so far:

http://localhost:8080/test_1?&product_id=in.(1,2)&container_id=in.(1,2)

This obviously returns all values as it is not correct.

Answer 1

As of PostgREST version 10, there is no operator that does that query directly. An alternative would be to do the filter using OR:

WHERE (product_id = 1 AND container_id = 1) OR
      (product_id = 1 AND container_id = 2) OR
      (product_id = 2 AND container_id = 1)

In PostgREST, that is:

http://localhost:3000/test_1?or=(and(product_id.eq.1,container_id.eq.1),and(product_id.eq.1,container_id.eq.2),and(product_id.eq.2,container_id.eq.1))

It gives the same result, although the syntax is more verbose in this case (not sure about the performance difference, though).