Compose SQL Queries

Question

I'm trying to implement a simple search engine on a Rails5 app.

Basically, I have several fields in a form and I need to fetch all the records that match all the input values. Note that all values are optional.

The values I can search for are name, description, created_at.

My idea is to create a clause for each value and then join them together.

class EmployeeSearch < ApplicationRecord
  self.table_name = 'employees'


  def results
    # MAGIC HAPPENS HERE
  end

 private

 def table
  Employee.arel_table
 end

 def name_condition 
  table[:name].eq(name)
 end

def description_condition
  table[:description].matches("%#{description}%") unless description.blank?
 end

def created_at_condition
 ...
end

end

EmployeeSearch.new(name: 'John Doe', created_at: '01/01/2010')

Now, How can I loop through all the conditions in results and chain each one on a where clause?

I was thinking something like

methods.grep(/_condition$/).map { |c| where(send(c)) }

or similar but I cannot make it work.

Any suggestions?

Thanks

Answer 1

Yes, use chaining for this. I would put your filter fields in an array, for example filter[name]

Then you could do something like this:

@employees = Employee
if params[:filter]
  params[:filter].each do |f_name, f_value|
    next if f_value.blank?
    @employees = @employees.where(f_name => f_value)
  end
end

Sorry couldn't test it, but should work

Answer 2

Your example would work but you need to place the where clause on the association:

methods.grep(/_condition$/).each do |condition|
  association = association.where(send(condition))
end