NothingToSeeHere NothingToSeeHere - 1 year ago 71
Ruby Question

Rails 4 Using Ruby Enumerable to query arrays using arrays

I have a model that stores an array in one of the table columns called 'attributes'. So 3 separate records might look like this:

Record 1

MyModel.attributes = {Red, Furry, Stinky}

Record 2

MyModel.attributes = {Red}

Record 3

MyModel.attributes = nil

Record 4

MyModel.attributes = {Blue, Furry, Sweet}

I'd like to query this array for any of another array, including nil. The results should return any records that have any of the attributes in the query array and any records where the attributes column is nil.

query_array = [Blue, Furry]

The answer to this query should provide Record 1, Record 3 and Record 4 -- again, it's not looking for ALL the

currently, I can do this if I just query {|m| m.attributes["Furry"] or m.attributes["Blue"] }

But I want to be able to create the array dynamically and not handcode the m.attributes["attribute"]. I can't quite figure out how to do this without requiring all of the array items, I just want ANY of the array items and records with no attributes.

Answer Source

You probably should not call the column attributes as this name is already used for the attributes hash accessors in rails models. For the examples below i renamed this to tags

A simple solution would be to check for nil (always include those records) and check if the intersection of tags has any tags in it:

model_1.tags # => ['red', 'furry', 'stinky']
model_2.tags # => ['red']
model_3.tags # => nil
model_4.tags # => ['blue', 'furry', 'stinky']

search_tags = ['red', 'blue'] do |model|
  model.tags.nil? || (model.tags & search_tags).any?

You could also write it as a nested loop:

search_tags = ['red', 'blue'] do |model|
  model.tags.nil? || model.tags.any? { |tag| search_tags.include?(tag) }

This is all done in memory, in ruby itself. If you have 100_000 or 1_000_000 records, then all of them are fetched from DB, instantiated and then filtered.

So depending on your exact requirements and what DB you are using you could find an easier/more performant solution. Some ideas:

  • Store the tags in a separate table
  • Store the tags as a comma separated string and use a 'like' query
  • Use postgres JSON datatype and the query features postgres provides
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download