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 get 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)

Anybody have a workaround for this?

Answer 1

There's no easy way to query within a serialised Hash via ActiveRecord and SQL, unless you use a LIKE in your query (but this can't do comparisons like > and <).

Based on your use case you should really rethink your data model and normalise these fields into proper models/columns.