postgresql 如何查询存储在数组中的 Rails ActiveRecord 数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32387449/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I query Rails ActiveRecord data stored in arrays
提问by bdougie
I have a rails model call MentorData and it has an attribute called os_usage. The oses are stored in an array like so ['apple', 'linux'].
我有一个名为 MentorData 的 rails 模型，它有一个名为os_usage. oses 像这样存储在一个数组中['apple', 'linux']。
To recap:
回顾一下：
$ MentorData.first.os_usage
=> ['apple', 'linux']
I am looking to be able to query the data for all MentorData that includes the os_usage of apple, but when I search MentorData.where(os_usage: 'apple')I only get the mentors who can only use apple and not apple and linux. I need to search in some way that checks if apple is included in the array.
我希望能够查询包含 os_usage 的所有 MentorData 的数据apple，但是当我搜索时，MentorData.where(os_usage: 'apple')我只得到只能使用 apple 而不能使用 apple 和 linux 的导师。我需要以某种方式搜索以检查数组中是否包含苹果。
I have also tried the following.
我也尝试了以下方法。
MentorData.where('os_usage like ?', 'apple')
MentorData.where('os_usage contains ?', 'apple')
MentorData.where('os_usage contains @>ARRAY[?]', 'apple')
Is it possible to query data in ActiveRecord by attributes that have an array or items?
是否可以通过具有数组或项目的属性查询 ActiveRecord 中的数据？
The database is on Postgres if that helps in providing a more raw search query.
如果这有助于提供更原始的搜索查询，则该数据库位于 Postgres 上。
回答by David Aldridge
Here are the examples given in the current Rails Edge Guides:
以下是当前Rails Edge Guides 中给出的示例：
# db/migrate/20140207133952_create_books.rb
create_table :books do |t|
t.string 'title'
t.string 'tags', array: true
t.integer 'ratings', array: true
end
add_index :books, :tags, using: 'gin'
add_index :books, :ratings, using: 'gin'
# app/models/book.rb
class Book < ActiveRecord::Base
end
# Usage
Book.create title: "Brave New World",
tags: ["fantasy", "fiction"],
ratings: [4, 5]
## Books for a single tag
Book.where("'fantasy' = ANY (tags)")
## Books for multiple tags
Book.where("tags @> ARRAY[?]::varchar[]", ["fantasy", "fiction"])
## Books with 3 or more ratings
Book.where("array_length(ratings, 1) >= 3")
回答by Zepplock
Have you tried MentorData.where("'apple' = ANY (os_usage)")?
你试过MentorData.where("'apple' = ANY (os_usage)")吗？
回答by emancu
Maybe you should detach the os_usagearray from your model and make it a separate table.
也许您应该将os_usage数组从模型中分离出来并使其成为一个单独的表。
In ActiveRecord world you will get something like the following code:
在 ActiveRecord 世界中，您将获得类似于以下代码的内容：
class MentorData < ActiveRecord::Base
..
has_and_belongs_to_many :os_usage
..
end
class OsUsage < ActiveRecord::Base
..
has_and_belongs_to_many :mentors_data
..
end
Creating a many_to_manyrelationship between this two models, allows you to query easily and avoid duplications. This technique is called normalization.
many_to_many在这两个模型之间创建关系，可以让您轻松查询并避免重复。这种技术称为规范化。
Using this new designyou have your collection of os_usagemade by objects instead of strings
使用这种新设计，您可以使用对象而不是字符串来收集os_usage
MentorData.first.os_usage
# => [#<OsUsage:....>, #<OsUsage:...>]
Which you can convert easy into the old array of strings
您可以轻松地将其转换为旧的字符串数组
MentorData.first.os_usage.map(&:name)
# => ['apple', 'linux']
In addition, you can query the data for all MentorData that includes the os_usage of apple:
此外，您可以查询包含apple的 os_usage 的所有 MentorData 的数据：
MentorData.joins(:os_usages).where('os_usages.name' => 'apple')
And also query all the MentorData records for an OsUsage:
并查询 OsUsage 的所有 MentorData 记录：
OsUsage.where(name: 'apple').mentors_data
I hope you find it useful :)
希望对你有帮助 ：）
回答by NullVoxPopuli
For like queries, you need %% to indicate that text can appear on the left or right of your search.
对于同类查询，您需要 %% 来指示文本可以出现在搜索的左侧或右侧。
So, try
所以，试试
MentorData.where('os_usage LIKE "%apple%"')
MentorData.where('os_usage LIKE "%apple%"')
and see if that works.
看看这是否有效。
It is a wild card search, but omitting the % operates like =
这是一个通配符搜索，但省略 % 的操作类似于 =
See this question: SQL LIKE with no wildcards the same as '='?
看到这个问题：SQL LIKE with no wildcards as '='?
This assumes os_usageis a serialized array, where the column backing that data is a string, and rails deserializes when instantiating your MentorData
这假设os_usage是一个序列化的数组，其中支持该数据的列是一个字符串，并且 rails 在实例化您的数据时反序列化MentorData
Edit: I'd find out how your db is storing the array, so maybe you could do
编辑：我会找出你的数据库是如何存储数组的，所以也许你可以这样做
"%'apple'%"
to make sure that it doesn't select oses with apple just contained in the name.
以确保它不会选择名称中仅包含苹果的 oses。
