CODEWITHSUNDEEP
ruby-on-railspostgresqlrails-postgresqlhstore
ranjan
ranjan

Reputation: 11

Rails postgres hstore: query for a specific key with any of the given values

My question is specific to rails+postgres hstore datatype.

The WHERE IN [1,2, 3] or the rails equivalent Model.where(data: [1,2,3]) works fine for regular columns, but not for hstore.

I have a hstore column(say info) and I want to query for rows which have a particular key and any one of the given values.

For example: To find all books that have a key as 'author' and value as 'ABC' in hstore column, the following query works fine:

Book.where("info @> hstore(:key, :value)", key: "author", value: "ABC")

But I need a query that returns records which have a key as 'author' and any one of values in ['ABC', 'XYZ', 'PQRS', 'DFG'].

Any suggestions?

Upvotes: 1

Views: 1470

Answers (1)

lmz
lmz

Reputation: 1580

Maybe try:

Book.where("(info -> :key) IN (:values)", key: 'author', values: ['ABC', 'XYZ'])

However, @> has index support, while this won't use any indexes.

Upvotes: 1

Related Questions