Combine multiple areas or where queries with OR

Question

How to get isl components in such a way 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 ran into some problems with accepted answers to similar questions.

Let's say that I have this class:

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 OR.

One way is Patient.with_name_like("Susan") | Patient.with_name_like("Dave"). It seems that each individual request is launched separately, and not combined into a single request. I have decided this solution.

Another method that only works in some cases:

# 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

So, problems with ORing queries occur when wherea string is passed or the query requires a join.

, where , isl, . .

, ActiveRecord AREL, .

Answer 1

, Ransack?

, / .

, yours, /, , or, ransack , , javascript , , - .

ransack. :

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

<%= 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 %>

, anding oring, , ransack.

Answer 2

. , , , , .. , , - :/p >

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

:

...

<%= 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>

...

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

. , , . .