1

Given a table games and column identifiers, whose type is HSTORE:

| id | name             | identifiers                        |
|----|------------------|------------------------------------|
| 1  | Metal Gear       | { sku: 109127072, ean: 512312342 } |
| 2  | Theme Hospital   | { sku: 399348341 }                 |
| 3  | Final Fantasy    | { ean: 109127072, upc: 999284928 } |
| 4  | Age of Mythology | { tbp: 'a998fa31'}                 |
| 5  | Starcraft II     | { sku: 892937742, upc: 002399488 } |

How can I find if a given set of key-value pairs has at least one match in the database?

For example, if I supply this array: [ {sku: 109127072 }, { upc: 999284928 } ], I should see:

| id | name           | identifiers                        |
|----|----------------|------------------------------------|
| 1  | Metal Gear     | { sku: 109127072, ean: 512312342 } |
| 3  | Final Fantasy  | { ean: 109127072, upc: 999284928 } |
Wilson Silva
  • 10,046
  • 6
  • 26
  • 31

1 Answers1

0

For rails 5 you, think, should try using or operator like:

h = { sku: 109127072, upc: 999284928 }
rela = Person.where("identifiers -> ? = ?", h.keys.first, h[h.keys.first])
h.keys[1..-1].reduce(rela) {|rela, key| rela.or("identifiers -> ? = ?", key, h[key]) }
# => relation with ored-arguments

for non 5-th rails you shall use arel as descibed here.

Community
  • 1
  • 1
Малъ Скрылевъ
  • 16,187
  • 5
  • 56
  • 69
  • Thanks. The query that I'm looking for assumes that I don't know the keys of the `identifiers` column. I can't write an OR condition for every single identifier that the user might add. – Wilson Silva May 23 '16 at 13:56