Reputation: 432
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?
Upvotes: 4
Views: 10100
Reputation: 1860
If you're using PostgreSQL with JSONB fields to store your hashes, you can drop down to SQL in the where
and use something like this:
City.where("cast(cached_info ->> 'population' as int) > ?", 300)
The ->>
is a Postgres JSONB access operator -- check out this article.
Upvotes: 1
Reputation: 9669
As Jits said, LIKE/ILIKE would work, e.g.:
City.where('cached_info LIKE ?', '%name: Louisiana%')
Upvotes: 4
Reputation: 9748
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.
Upvotes: 7