Activerecord 其中带有哈希值
我有一个带有 cached_info 字段的城市模型，该字段是序列化哈希。
{:population=>20000, :more_stuff =>....}
如果我在 Activerecord 中进行以下查询。
City.where('cached_info[:population] > 300').count
我回来了...
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)
有人有解决方法吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
没有简单的方法可以通过 ActiveRecord 和 SQL 在序列化哈希中进行查询，除非您在查询中使用
LIKE（但这不能进行像>和 <代码><）。根据您的用例，您应该真正重新考虑您的数据模型并将这些字段规范化为正确的模型/列。
There's no easy way to query within a serialised Hash via ActiveRecord and SQL, unless you use a
LIKEin 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.
正如 Jits 所说，LIKE/ILIKE 可以工作，例如：
As Jits said, LIKE/ILIKE would work, e.g.:
如果您使用带有 JSONB 字段的 PostgreSQL 来存储哈希值，则可以下拉到
where中的 SQL 并使用如下内容：->>是Postgres JSONB 访问运算符 -- 查看这篇文章。If you're using PostgreSQL with JSONB fields to store your hashes, you can drop down to SQL in the
whereand use something like this:The
->>is a Postgres JSONB access operator -- check out this article.