Ruby-on-rails 使用活动记录搜索序列化数据

Searching serialized data, using active record

提问by JZ.

I'm trying to do a simple query of a serialized column, how do you do this?

我正在尝试对序列化列进行简单查询，您是如何执行此操作的？

serialize :mycode, Array


1.9.3p125 :026 > MyModel.find(104).mycode
  MyModel Load (0.6ms)  SELECT `mymodels`.* FROM `mymodels` WHERE `mymodels`.`id` = 104 LIMIT 1
 => [43565, 43402] 
1.9.3p125 :027 > MyModel.find_all_by_mycode("[43402]")
  MyModel Load (0.7ms)  SELECT `mymodels`.* FROM `mymodels` WHERE `mymodels`.`mycode` = '[43402]'
 => [] 
1.9.3p125 :028 > MyModel.find_all_by_mycode(43402)
  MyModel Load (1.2ms)  SELECT `mymodels`.* FROM `mymodels` WHERE `mymodels`.`mycode` = 43402
 => [] 
1.9.3p125 :029 > MyModel.find_all_by_mycode([43565, 43402])
  MyModel Load (1.1ms)  SELECT `mymodels`.* FROM `mymodels` WHERE `mymodels`.`mycode` IN (43565, 43402)
 => [] 

采纳答案by noodl

Basically, you can't. The downside of #serialize is that you're bypassing your database's native abstractions. You're pretty much limited to loading and saving the data.

基本上，你不能。#serialize 的缺点是您绕过了数据库的本机抽象。您几乎只能加载和保存数据。

That said, one very good way to slow your application to a crawl could be:

也就是说，降低应用程序爬行速度的一种非常好的方法可能是：

MyModel.all.select { |m| m.mycode.include? 43402 }

Moral of the story: don't use #serialize for any data you need to query on.

这个故事的寓意：不要将#serialize 用于您需要查询的任何数据。

回答by rks

It's just a trick to not slow your application. You have to use .to_yaml.

这只是一个不减慢应用程序的技巧。你必须使用.to_yaml.

exact result:

准确结果：

MyModel.where("mycode = ?", [43565, 43402].to_yaml)
#=> [#<MyModel id:...]

Tested only for MySQL.

仅针对 MySQL 进行了测试。

回答by jbmyid

Serialized array is stored in database in particular fashion eg:

序列化数组以特定方式存储在数据库中，例如：

[1, 2, 3, 4]
in
1\n 2\n 3\n etc

hence the query would be

因此查询将是

MyModel.where("mycode like ?", "% 2\n%")

put space between %and 2.

在%和之间放置空格2。

回答by konung

Noodl's answer is right, but not entirely correct.

Noodl 的回答是正确的，但并不完全正确。

It really depends on the database/ORM adapter you are using: for instance PostgreSQL can now store and search hashes/json - check out hstore. I remember reading that ActiveRecord adapter for PostgreSQl now handles it properly. And if you are using mongoid or something like that - then you are using unstructured data (i.e. json) on a database level everywhere.

这实际上取决于您使用的数据库/ORM 适配器：例如 PostgreSQL 现在可以存储和搜索哈希/json - 查看 hstore。我记得读过 PostgreSQl 的 ActiveRecord 适配器现在可以正确处理它。如果您使用的是 mongoid 或类似的东西 - 那么您就在数据库级别上到处使用非结构化数据（即 json）。

However if you are using a db that can't really handle hashes - like MySQL / ActiveRecord combination - then the only reason you would use serialized field is for somet data that you can create / write in some background process and display / output on demand - the only two uses that I found in my experience are some reports ( like a stat field on a Product model - where I need to store some averages and medians for a product), and user options ( like their preferred template color -I really don't need to query on that) - however user information - like their subscription for a mailing list - needs to be searchable for email blasts.

但是，如果您使用的数据库无法真正处理散列（例如 MySQL / ActiveRecord 组合），那么您使用序列化字段的唯一原因是您可以在某些后台进程中创建/写入某些数据并按需显示/输出- 我在我的经验中发现的唯一两个用途是一些报告（例如产品模型上的统计字段 - 我需要在其中存储产品的一些平均值和中位数）和用户选项（例如他们喜欢的模板颜色 - 我真的不需要查询）——但是用户信息——比如他们对邮件列表的订阅——需要可搜索电子邮件群发。

PostgreSQL hstoreActiveRecord Example:

MyModel.where("mycode @> 'KEY=>\"#{VALUE}\"'")

PostgreSQL hstoreActiveRecord 示例：

MyModel.where("mycode @> 'KEY=>\"#{VALUE}\"'")

UPDATEAs of 2017 both MariaDB and MySQL support JSON field types.

更新截至 2017 年，MariaDB 和 MySQL 都支持 JSON 字段类型。

回答by NealJMD

Good news! If you're using PostgreSQL with hstore (which is super easy with Rails 4), you can now totally search serialized data. Thisis a handy guide, and here's the syntax documentation from PG.

好消息！如果您将 PostgreSQL 与 hstore 一起使用（这在 Rails 4 中非常简单），您现在可以完全搜索序列化数据。这是一个方便的指南，这里是 PG 的语法文档。

In my case I have a dictionary stored as a hash in an hstore column called amenities. I want to check for a couple queried amenities that have a value of 1in the hash, I just do

在我的例子中，我有一个字典作为哈希存储在一个名为amenities. 我想检查几个1在哈希中具有 值的查询设施，我只是这样做

House.where("amenities @> 'wifi => 1' AND amenities @> 'pool => 1'")

Hooray for improvements!

万岁改进！

回答by Kyle C

You can query the serialized column with a sql LIKE statement.

您可以使用 sql LIKE 语句查询序列化列。

 MyModel.where("mycode LIKE '%?%'", 43402)

This is quicker than using include?, however, you cannot use an array as the parameter.

这比使用 include? 更快，但是，您不能使用数组作为参数。

回答by Kyle C

There's a blog post from 2009 from FriendFeedthat describes how to use serialized data within MySQL.

FriendFeed 于 2009 年发表了一篇博客文章，描述了如何在 MySQL 中使用序列化数据。

What you can do is create tables that function as indexes for any data that you want to search.

您可以做的是创建用作要搜索的任何数据的索引的表。

Create a model that contains the searchable values/fields

创建一个包含可搜索值/字段的模型

In your example, the models would look something like this:

在您的示例中，模型如下所示：

class MyModel < ApplicationRecord
  # id, name, other fields...
  serialize :mycode, Array
end

class Item < ApplicationRecord
  # id, value...
  belongs_to :my_model
end

Creating an "index" table for searchable fields

为可搜索字段创建“索引”表

When you save MyModel, you can do something like this to create the index:

保存 MyModel 时，您可以执行以下操作来创建索引：

Item.where(my_model: self).destroy
self.mycode.each do |mycode_item|
  Item.create(my_model: self, value: mycode_item)
end

Querying and Searching

查询和搜索

Then when you want to query and search just do:

然后，当您要查询和搜索时，只需执行以下操作：

Item.where(value: [43565, 43402]).all.map(&:my_model)
Item.where(value: 43402).all.map(&:my_model)

You can add a method to MyModel to make that simpler:

您可以向 MyModel 添加一个方法以使其更简单：

def find_by_mycode(value_or_values)
  Item.where(value: value_or_values).all.map(&my_model)
end

MyModel.find_by_mycode([43565, 43402])
MyModel.find_by_mycode(43402)

To speed things up, you will want to create a SQL index for that table.

为了加快速度，您需要为该表创建一个 SQL 索引。

回答by Jignesh Gohel

Using the following comments in this post

在这篇文章中使用以下评论

https://stackoverflow.com/a/14555151/936494

https://stackoverflow.com/a/14555151/936494

https://stackoverflow.com/a/15287674/936494

https://stackoverflow.com/a/15287674/936494

I was successfully able to query a serialized Hash in my model

我成功地在我的模型中查询了一个序列化的哈希

class Model < ApplicationRecord
  serialize :column_name, Hash
end

When column_nameholds a Hash like

当column_name持有一个哈希像

{ my_data: [ { data_type: 'MyType', data_id: 113 } ] }

we can query it in following manner

我们可以通过以下方式查询它

Model.where("column_name = ?", hash.to_yaml)

That generates a SQL query like

这会生成一个 SQL 查询，如

Model Load (0.3ms)  SELECT "models".* FROM "models" WHERE (column_name = '---
:my_data:
- :data_type: MyType
  :data_id: 113
')

In case anybody is interested in executing the generated query in SQL terminal it should work, however care should be taken that value is in exact format stored in DB. However there is another easy way I found at PostgreSQL newline characterto use a raw string containing newline characters

如果有人有兴趣在 SQL 终端中执行生成的查询，它应该可以工作，但应注意值的格式是否准确存储在 DB 中。但是，我在PostgreSQL 换行符中找到了另一种简单的方法来使用包含换行符的原始字符串

select * from table_name where column_name = E'---\n:my_data:\n- :data_type: MyType\n  :data_id: 113\n'

The most important part in above query is E.

上述查询中最重要的部分是E.

Note: The database on which I executed above is PostgreSQL.

注意：我上面执行的数据库是PostgreSQL。