CODEWITHSUNDEEP
arraysrubypostgresqlactiverecordrails-activerecord
exchez
exchez

Reputation: 503

Using ActiveRecord to match an array in a record

One column of my table consists of an array of strings in json format. I want to know how to use ActiveRecord to return all records that have an array that matches or is comprised of the same items in the array provided.

For example:

flag.colors = ['red', 'blue', 'green']
query = ['blue', 'red', 'green']

Flag.where('colors matches query') #=> flag

Upvotes: 1

Views: 2253

Answers (1)

Babar Al-Amin
Babar Al-Amin

Reputation: 3984

Searching serialized data with ActiveRecord on SQL part won't be possible. You can filter it in Ruby end though:

flag.colors = ['red', 'blue', 'green']
query = ['blue', 'red', 'green']

Flag.all.select{|f| f.colors.sort == query.sort} #=> [flag]

As you're using PostgreSQL, there's a better way actually. PG supports native Array. You can enable it in migration:

create_table :flags do |t|
  t.text :tags, array: true
end

Then simply search for it:

query = ['blue', 'red', 'green']

#With Overlap operator:
Flag.where.overlap(tags: query)

#or with Contains Operator
Flag.where.contains(tags: query)

You can find more details here.

Upvotes: 2

Related Questions