CODEWITHSUNDEEP
ruby-on-railspostgresqlhstore
Bruno Campos
Bruno Campos

Reputation: 2188

Postgres hstore, how to query for arrays containing values?

[All codes here are samples] I have the Book model with the info field as hstore.

class Book < ActiveRecord::Base
  serialize :info, ActiveRecord::Coders::NestedHstore
end

and the info field is filled like this:

info: {"tags" => ["tag one", "tag two"]}

I need a query that I can find the books that contains the tag "tag one". How can I accomplish this?

Upvotes: 0

Views: 1355

Answers (1)

max
max

Reputation: 102036

You might not want to use a a hstore column here at all. Since hstore stores the values as plain text you need to do something like:

Book.where("WHERE info->'tags' LIKE '%?%'", "bestseller");

Seems like a decent idea until you realize that you are doing a full text search on a comma delimited string.

Instead you might want to use a tags table and a book_tags join table the good old way.

Upvotes: 1

Related Questions