Combine multiple scope or where queries with OR

Question

How do I get the arel components in such a ways that I can do something like:

queries = []
queries << MyModel.some_scope.get_the_arel_component
queries << MyModel.some_scope_with_param("Dave").get_the_arel_component
queries << MyModel.where(:something => 'blah').get_the_arel_component
queries << MyModel.some_scope_with_join_and_merge.get_arel_component
# etc ... (may be any number of queries) 

# join each query with OR
combined_query = nil
queries.each do |query|
  combined_query ||= query
  combined_query = combined_query.or(q)
end

# run the query so it just works
MyModel.where(combined_query)

I've encountered some issues with accepted answers of similar questions.

Lets say I have a class like so:

class Patient
  has_one :account

  scope :older_than, ->(date) { where(arel_table[:dob].lt(date)) }
  scope :with_gender, ->(gender) { where(:gender => gender) }
  scope :with_name_like, ->(name) { where("lower(name) LIKE ?", name.downcase) }
  scope :in_arrears, -> { joins(:account).merge( Account.in_arrears ) } 
end

The goal is to combine any scope or where clause with an OR.

One way would be Patient.with_name_like("Susan") | Patient.with_name_like("Dave"). This seems to run each individual query separately instead of combine into a single query. I've ruled this solution out.

Another method that only works in some instances is:

# this fails because `where_values` for the `with_name_like` scope returns a string
sues = Patient.with_name_like("Susan").where_values.reduce(:and)
daves = Patient.with_name_like("Dave").where_values.reduce(:and)
Patient.where(sues.or(daves))

# this works as `where_values` returns an `Arel::Nodes::Equality` object
ages = Patient.older_than(7.years.ago).where_values.reduce(:and)
males = Patients.with_gender('M').where_values.reduce(:and)
Patient.where(ages.or(males))

# this fails as `in_arrears` scope requires a joins
of_age = Patient.older_than(18.years.ago).where_values.reduce(:and)
arrears = Patients.in_arrears.where_values.reduce(:and)
Patient.where(of_age.or(arrears)) # doesn't work as no join on accounts
Patient.join(:account).where(of_age.or(arrears)) # does work as we have our join

To sum up, the issues with ORing queries arise when where is passed a string or the query requires a join.

I'm pretty sure where converts anything passed to it into an arel object, it's just a matter of getting access to the correct pieces and recombining them in the correct way. I just haven't managed to work it out yet.

Preferably the answer will only make use of ActiveRecord and AREL and not a third party library.

Answer 1

Since you're open to using a third party library, how about Ransack?

It has a very robust implementation allowing for all kinds of and and or condition combinations and works well with associated models as well.

For a use case like yours where there are a few predefined queries/scopes that I want the user to be able to select from and run the or combination of them, I use ransack's out of the box implementation and then on the view level, I use javascript to insert hidden fields with values that will result in the structured params hash ransack is expecting in the controller.

All of your scopes are simple to define in a view using ransack helpers. Your code should look like:

Controller

def index
  @q = Patient.search(params[:q])
  @patients = @q.result(distinct: true)
end

View

<%= search_form_for @q do |f| %>
  <%= f.label :older_than %>
  <%= f.date_field :dob_lt %>
  <%= f.label :with_gender %>
  <%= f.text_field :gender_eq %>
  <%= f.label :with_name_like %>
  <%= f.text_field :name_cont %>
  <%= f.label :in_arrears_exceeding %>
  <%= f.text_field :accounts_total_due_gte %>
  <%= f.submit %>
<% end %>

Also, if you want more control over the anding and oring take a look at the complex search form builder example using ransack.

Answer 2

I had worked on a similar problem in one of my previous projects. The requirement was to find a set of volunteers to scribe matching a set of criteria like email, location, stream of study etc. The solution that worked for me is to define fine-grained scopes and writing up my own query builder like this:

class MatchMaker
  # Scopes
  #   Volunteer => [ * - 'q' is mandatory, # - 'q' is optional, ** - 's', 'e' are mandatory ]
  #     active  - activation_state is 'active'
  #     scribes - type is 'scribe'
  #     readers - type is 'reader'
  #     located - located near (Geocoder)
  #     *by_name  - name like 'q'
  #     *by_email - email like 'q'
  #     educated - has education and title is not null
  #     any_stream - has education stream and is not null
  #     *streams - has education stream in 'q'
  #     #stream - has education stream like 'q'
  #     #education - has education and title like 'q'
  #     *level - education level (title) is 'q'
  #     *level_lt - education level (title) is < 'q'
  #     *level_lteq - education level (title) is <= 'q'
  #     *marks_lt - has education and marks obtained < 'q'
  #     *marks_lteq - has education and marks obtained <= 'q'
  #     *marks_gt - has education and marks obtained > 'q'
  #     *marks_gteq - has education and marks obtained >= 'q'
  #     *knows - knows language 'q'
  #     *reads - knows and reads language 'q'
  #     *writes - knows and writes language 'q'
  #     *not_engaged_on - doesn't have any volunteering engagements on 'q'
  #     **not_engaged_between - doesn't have any volunteering engagements betwee 'q' & 'q'
  #     #skyped - has skype id and is not null
  def search(scope, criteria)
    scope = scope.constantize.scoped

    criteria, singular = singular(criteria)
    singular.each do |k|
        scope = scope.send(k.to_sym)
    end

    if criteria.has_key?(:not_engaged_between)
      multi = criteria.select { |k, v| k.eql?(:not_engaged_between) }
      criteria.delete(:not_engaged_between)

      attrs = multi.values.flatten
      scope = scope.send(:not_engaged_between, attrs[0], attrs[1])
    end

    build(criteria).each do |k, v|
        scope = scope.send(k.to_sym, v)
    end

    scope.includes(:account).limit(Configuration.service_requests['limit']).all
  end

  def build(params)
    rejects = ['utf8', 'authenticity_token', 'action']
    required = ['by_name', 'by_email', 'by_mobile', 'streams', 'marks_lt', 'marks_lteq', 'marks_gt', 
      'marks_gteq', 'knows', 'reads', 'writes', 'not_engaged_on', 'located', 'excluding', 
      'level', 'level_lt', 'level_lteq']
    optional = ['stream', 'education']

    params.delete_if { |k, v| rejects.include?(k) }
    params.delete_if { |k, v| required.include?(k) && v.blank? }
    params.each { |k, v| params.delete(k) if optional.include?(k.to_s) && v.blank? }

    params
  end

  def singular(params)
    pattrs   = params.dup
    singular = ['active', 'scribes', 'readers', 'educated', 'any_stream', 'skyped']
    original = []

    pattrs.each { |k, v| original << k && pattrs.delete(k) if singular.include?(k.to_s) }

    [pattrs, original]
  end
end

The form would be something like this:

...

<%= f.input :paper ... %>

<%= f.input :writes ... %>

<%= f.input :exam_date ... %>

<%= f.time_select :start_time, { :combined => true, ... } %>

<%= f.time_select :end_time, { :combined => true, ... } %>

<fieldset>
  <legend>Education criteria</legend>

  <%= f.input :streams, :as => :check_boxes, 
    :collection => ..., 
    :input_html => { :title => 'The stream(s) from which the scribe can be taken' } %>

  <%= f.input :education, :as => :select, 
    :collection => ...,
    :input_html => { :class => 'input-large', :title => configatron.scribe_request.labels[:education]}, :label => configatron.scribe_request.labels[:education] %>

  <%= f.input :marks_lteq, :label => configatron.scribe_request.labels[:marks_lteq], 
    :wrapper => :append do %>
    <%= f.input_field :marks_lteq, :title => "Marks", :class => 'input-mini' %>
    <%= content_tag :span, "%", :class => "add-on" ... %>
  <% end %> 
</fieldset>

...

And finally

# Start building search criteria
criteria = service_request.attributes
...
# do cleanup of criteria
MatchMaker.new.search('<Klass>', criteria)

This has worked for me very well in the past. Hope this would lead you in the right direction in solving the problems you are facing. All the best.