CODEWITHSUNDEEP
ruby-on-rails-3postgresqlactiverecord
ac360
ac360

Reputation: 7835

Active Record Query - Search Multiple Columns for Multiple Strings and Return Only if They Are All Included

I need help designing a query via Active Record & Postgresql.

• The query must search across all of the following columns...

The Model looks like this:

Collection

 item_id1: String
 item_id2: String
 item_id3: String
 item_id4: String
 item_id5: String
 item_id6: String

• The query needs to pass in an array of strings that need to be searched across all of the item_id fields.

• The query must also only return results of Records containing all of the strings within the array.

Note: I also have the Textacular Full Text Search gem installed. However, I tested a search that I believe is supposed to search and return matches only if the records include all of the passed in strings, and the search came up with nothing, despite records with those strings existing in my database. Like this: Collection.advanced_search('B0066AJ5TK&B0041KJSL2')

Upvotes: 1

Views: 1029

Answers (1)

MrTheWalrus
MrTheWalrus

Reputation: 9700

Just to clarify: You want records where each of the strings in the array are found somewhere within the six item_id fields?

There's probably a more elegant way to do this, but here's what I've got off the top of my head:

terms = ['foo', 'bar', 'baz']

conditions = []
values = {}

terms.each_with_index do |t,i|
  arg_id = "term#{i}".to_sym
   conditions << "(item_id1 = :#{arg_id} OR item_id2 = :#{arg_id} OR item_id3 = :#{arg_id} OR item_id4 = :#{arg_id} OR item_id5 = :#{arg_id} OR item_id6 = :#{arg_id})"
   values[arg_id] = t
end

Collection.where(conditions.join(' AND '), values)

This should produce a query like this:

SELECT "collections".* FROM "collections" WHERE ((item_id1 = 'foo' OR item_id2 = 'foo' OR item_id3 = 'foo' OR item_id4 = 'foo' OR item_id5 = 'foo' OR item_id6 = 'foo') AND (item_id1 = 'bar' OR item_id2 = 'bar' OR item_id3 = 'bar' OR item_id4 = 'bar' OR item_id5 = 'bar' OR item_id6 = 'bar') AND (item_id1 = 'baz' OR item_id2 = 'baz' OR item_id3 = 'baz' OR item_id4 = 'baz' OR item_id5 = 'baz' OR item_id6 = 'baz'))

Which is long and ugly, but should get the results you want.

If you meant that the fields might contain the strings to be searched for, rather than be equal to them, you could instead use

item_id1 LIKE #{arg_id}

and

values[arg_id] = "%#{t}%"

Upvotes: 2

Related Questions