CODEWITHSUNDEEP
sqlruby-on-railsrubyactiverecord
stuartchaney
stuartchaney

Reputation: 432

Activerecord Where with a hash value

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

Answers (3)

Tom Hundt
Tom Hundt

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

thomax
thomax

Reputation: 9669

As Jits said, LIKE/ILIKE would work, e.g.:

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

Upvotes: 4

Jits
Jits

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

Related Questions