How can I query Rails ActiveRecord data stored in arrays

Question

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

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.

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?

The database is on Postgres if that helps in providing a more raw search query.

Solution 1

Here are the examples given in the current 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")

Solution 2

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

Solution 3

Maybe you should detach the os_usage array from your model and make it a separate table.

In ActiveRecord world you will get something like the following code:

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.

Using this new design you have your collection of os_usage made by objects instead of strings

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:

MentorData.joins(:os_usages).where('os_usages.name' => 'apple')

And also query all the MentorData records for an OsUsage:

OsUsage.where(name: 'apple').mentors_data

I hope you find it useful :)

Comments

[David Aldridge]

The Rails Guides tell you how to do this, and many more PostgreSQL-specific operations: edgeguides.rubyonrails.org/active_record_postgresql.html

[bdougie]

I had to make a change, but %'s worked. MentorData.where('os_usage LIKE ?', "%apple%")

[bdougie]

Another note: ActiveRecord actually makes the array a string, which is why this works

[NullVoxPopuli]

@mu is too short, sorry. I made the assumption that os_usage is a serialized column, stored as a string, and then deserialized back in to an array. brianllamar, glad it worked :-)

[David Stosik]

This is not a perfect answer. For the sake of the example, imagine a new OS called "IN OS" gets released and is named in in your database. Then when you search for records containing in, you'll write the query MentorData.where('os_usage LIKE "%in%"') and also grab all records that contain linux and windows, although they might not contain in as in "IN OS".

[bdougie]

This is a great write up and makes a lot of sense. It seems like a better approach than my current.