How can I request Rails ActiveRecord data stored in arrays

Question

I have a call rail model MentorData and it has the os_usage attribute. Oz are stored in an array like ['apple', 'linux'] .

Repeat:

 $ MentorData.first.os_usage => ['apple', 'linux'] 

I am looking to be able to query data for all MentorData that include os_usage apple , but when I search MentorData.where(os_usage: 'apple') I only get mentors that can only use apple, not apple and linux. I need to somehow find what checks if the apple is included in the array.

I 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 using attributes that have an array or elements?

The database is in Postgres if it helps in providing a more crude search query.

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

Answer 2

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

Answer 3

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

In the ActiveRecord world, you 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 the many_to_many relationship between the two models makes it easy to query and avoid duplication. This method is called normalization.

Using this new design, you have an os_usage collection created by objects instead of strings

 MentorData.first.os_usage # => [#<OsUsage:....>, #<OsUsage:...>] 

What you can easily convert to an old string array

 MentorData.first.os_usage.map(&:name) # => ['apple', 'linux'] 

In addition, you can request data for all MentorData that include os_usage from apple:

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

And also request all the MentorData entries for OsUsage:

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

Hope you find this helpful :)

Answer 4

For such queries, you need %% to indicate that the text may appear to the left or right of your search.

So try

MentorData.where('os_usage LIKE "%apple%"')

and see if that works.

This is a wildcard search, but lowering% works like =

See this question: SQL LIKE without wildcards same as '='?

This suggests that os_usage is a serialized array, where the column supports this data as a string, and the rails are deserialized when instantiating MentorData

Edit: I would find out how your db stores the array, so maybe you could do

 "%'apple'%" 

to make sure that he doesn’t pick oses with the apple contained only in the name.