Dynamic query with multiple OR conditions

Question

On an ActiveRecord model, I'm trying to dynamically create a query that has multiple OR conditions. ie:

SELECT * from articles
WHERE (name LIKE '%a%' OR desc LIKE '%a%' OR
       name LIKE '%b%' OR desc LIKE '%b%' OR
       name LIKE '%c%' OR desc LIKE '%c%')

I think I'm on the right track using arel, but I can't work out how to start off the query.

class Article < ActiveRecord::Base

  attr_accessor :title, :text

  def self.search(terms)

    terms = *terms
    t = self.arel_table

    query = terms.reduce(???) do |query, word| 
      search_term = "%#{word}%"
      query.or(t[:title].matches(search_term).or(t[:text].matches(search_term)).expr).expr 
    end

    where(query)
  end

end

I originally got the idea from this answer, but the original query is a string obviously and not something I can chuck .or onto.

What do I need to replace ??? in the reduce method to make this work, or do I need to take a completely different path (as I suspect)?

score 4 · Answer 1 · answered Feb 06 '14 at 03:00

This is what I did to get it working:

class Article < ActiveRecord::Base

  attr_accessor :title, :text

  def self.search(terms)
    terms = *terms
    t = self.arel_table

    # generate array of conditions
    query = terms.collect do |word| 
      search_term = "%#{word}%"
      t[:title].matches(search_term).or(t[:text].matches(search_term)).expr 
    end

    # combine conditions
    query = query.reduce {|query, condition| query.or(condition).expr }

    where(query)
  end

end

Dynamic query with multiple OR conditions

1 Answers1