如何查询存储在数组中的Rails ActiveRecord数据

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'].

我有一个rails模型调用MentorData,它有一个名为os_usage的属性。 ose存储在一个像['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的数据,但是当我搜索MentorData.where(os_usage:'apple')时,我只会得到只能使用苹果而不是苹果和Linux的导师。我需要以某种方式搜索,检查数组中是否包含apple。

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上。

4 个解决方案

#1

Here are the examples given in the current Rails Edge Guides:

以下是当前Rails Edge指南中给出的示例:

# 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")

#2

Have you tried MentorData.where("'apple' = ANY (os_usage)")?

你试过MentorData.where(“'apple'= ANY(os_usage)”)吗?

#3

Maybe you should detach the os_usage array 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_many relationship between this two models, allows you to query easily and avoid duplications. This technique is called normalization.

在这两个模型之间创建many_to_many关系,可以轻松查询并避免重复。这种技术称为标准化。

Using this new design you have your collection of os_usage made 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 :)

希望对你有帮助 :)

#4

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没有与'='相同的通配符?

This assumes os_usage is 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.

确保它不会选择名称中包含苹果的ose。

#1