ActiveRecord Hstore Querys

active_record_hstore_queries.md

###Querying the database

Now you just need to learn a little bit of new sqls for selecting stuff (creating and updating is transparent). Find records that contains a key named 'foo’:

Person.where("data ? 'foo'")

Find records where 'foo’ is equal to 'bar’:

Person.where("data -> 'foo' = 'bar'")

This same sql is at least twice as fast (using indexes) if you do it that way:

Person.where("data @> 'foo=>bar'")

Find records where 'foo’ is not equal to 'bar’:

Person.where("data -> 'foo' <> 'bar'")

Find records where 'foo’ is like 'bar’:

Person.where("data -> 'foo' LIKE '%bar%'")

If you need to delete a key in a record, you can do it that way:

person.destroy_key(:data, :foo)

This way you’ll also save the record:

person.destroy_key!(:data, :foo)

The destroy_key method returns 'self’, so you can chain it:

person.destroy_key(:data, :foo).destroy_key(:data, :bar).save

But there is a shortcuts for that:

person.destroy_keys(:data, :foo, :bar)

And finally, if you need to delete keys in many rows, you can:

Person.delete_key(:data, :foo)

and with many keys:

Person.delete_keys(:data, :foo, :bar)

##Caveats

hstore keys and values have to be strings. This means true will become "true" and 42 will become "42" after you save the record. Only nil values are preserved.

It is also confusing when querying:

Person.where("data -> 'foo' = :value", value: true).to_sql
#=> SELECT "people".* FROM "people" WHERE ("data -> 'foo' = 't'") # notice 't'

To avoid the above, make sure all named parameters are strings:

Person.where("data -> 'foo' = :value", value: some_var.to_s)