Arel limit Where if fields are present

[Jean]

I trying to create a select using Arel but I’m passing three parameters, I want to include the parameters only if they are different to nil or blank.

Here is my method:

        def self.advsearch(summary_description, specialties, place)
User.joins(:experience,:summary,:information)
.where(Information.arel_table[:business].eq(false))
.where(Experience.arel_table[:description].matches("%#{summary_description}%") .or(Experience.arel_table[:description].matches("%#{summary_description.capitalize}%")) .or(Experience.arel_table[:job_title].matches("%#{summary_description}%"))
.or(Experience.arel_table[:job_title].matches("%#{summary_description.capitalize}%"))
.or(Summary.arel_table[:summary_description].matches("%#{summary_description}%"))
.or(Summary.arel_table[:specialties].matches("%#{specialties}%"))
.or(Information.arel_table[:address].matches("%#{place}%")))
.order
.uniq
end

I would like to do something like this:

        def self.advsearch(summary_description, specialties, place)
User.joins(:experience,:summary,:information)
.where(Information.arel_table[:business].eq(false))
.where(Experience.arel_table[:description].matches("%#{summary_description}%") unless !summary_description.nil?
.or(Experience.arel_table[:description].matches("%#{summary_description.capitalize}%")) unless !summary_description.nil?
.or(Experience.arel_table[:job_title].matches("%#{summary_description}%")) unless !summary_description.nil?
.or(Experience.arel_table[:job_title].matches("%#{summary_description.capitalize}%")) unless !summary_description.nil?
.or(Summary.arel_table[:summary_description].matches("%#{summary_description}%")) unless !summary_description.nil?
.or(Summary.arel_table[:specialties].matches("%#{specialties}%")) unless !summary.nil?
.or(Information.arel_table[:address].matches("%#{place}%"))) unless !place.nil?
.order
.uniq
end

I trying to google something similar, but I didn’t find anything like that.

Thanks in advance for your help.

[Matt_Jones]

The pieces don’t all have to be chained together in one line, so you can do something like this:

condition = Arel::Nodes::False.new

condition = condition.or(Experience.arel_table[:description].matches("%#{summary_description}%") unless summary_description.blank?

…etc, just keep ORing on additional things to condition…

User.joins(…).where(condition)

BTW, you probably won’t actually need the repetitions with capitalize - Arel implements the ‘matches’ operator with case-insensitive matching on DBs that are case-sensitive by default (for instance, here for PG: https://github.com/rails/arel/blob/master/lib/arel/visitors/postgresql.rb#L7 ).

Depending on the specific needs of your advanced search, you may also want to look into something like Sunspot (http://sunspot.github.com/), as it handles some fairly complicated query parsing and allows for multi-valued fields (great for things like ‘specialties’).

–Matt Jones