Activerecord Where with a hash value

Question

I have a city model with a cached_info field, which is a serialized hash.

{:population=>20000, :more_stuff =>....} 

If I make the following query in Activerecord.

 City.where('cached_info[:population] > 300').count 

I'm coming back...

 ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[:population] > 300)' at line 1: SELECT COUNT(*) FROM `places` WHERE `places`.`type` = 'City' AND (cached_info[:population] > 3) 

Does anyone have a workaround for this?

Answer 1

There is no easy way to query in serialized Hash through ActiveRecord and SQL unless you use LIKE in your query (but it cannot do comparisons such as > and < ).

Based on your use case, you should really rethink your data model and normalize these fields to the appropriate models / columns.

Answer 2

As Jits said, LIKE / ILIKE will work, for example:

 City.where('cached_info LIKE ?', '%name: Louisiana%') 

Answer 3

If you use PostgreSQL with JSONB fields to store your hashes, you can go down to SQL in where and use something like this:

 City.where("cast(cached_info ->> 'population' as int) > ?", 300) 

->> - this is the Postgres JSONB access statement - check out this article .