0

Let's assume I have the table with below columns and records:

id shop_id product_id
1  1       1
2  1       2
3  2       1
4  2       2

I want to run single query to get ID 1 and ID 4 records when query looks like this one:

ShopProduct.where(shop_id: 1, product_id: 1).where(shop_id: 2, product_id: 2)

The problem is that when I try to simplify the query like this, I get all four records:

ShopProduct.where(shop_id: [1,2], product_id: [1,2])
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Hubert Jakubiak
  • 853
  • 8
  • 19

1 Answers1

1

The two conditions are independent. This achieves a "step-lock" like you desire:

SELECT *
FROM   "ShopProduct"
WHERE  shop_id IN (1,2)
AND    product_id = shop_id; -- !

Or:

...
WHERE  shop_id = ANY ('{1,2}'::int[])
...

For best performance, have an index on shop_id or (shop_id, product_id) with this. See:

Should be something like this in Ruby:

ShopProduct.where('shop_id = ANY(ARRAY[?]::int[]) AND product_id = shop_id', ids.map { |i| i})

See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • But will it work if I change query to `ShopProduct.where(shop_id: 1, product_id: 1).where(shop_id: 2, product_id: 3)`? So shop_id does not equal product_id. Assuming that we have this extra record in the database. – Hubert Jakubiak Sep 04 '20 at 22:47
  • @HubertJakubiak: No, that's a different problem then. Your question indicated equality for both. Maybe ask another question? (There are solutions ...) Also define the use case more closely while being at it: few number pairs or long lists? Small or big table? Postgres version? Can matter for the best solution. – Erwin Brandstetter Sep 04 '20 at 22:50
  • Sorry, for bad example. Thanks for your input. – Hubert Jakubiak Sep 04 '20 at 22:54