Rails3: Combine Scope with Or

Rails3: combine scope with OR

From Arel documentation

The OR operator is not yet supported. It will work like this:
users.where(users[:name].eq('bob').or(users[:age].lt(25)))

This RailsCast shows you how to use the .or operator. However, it works with Arel objects while you have instances of ActiveRecord::Relation.
You can convert a relation to Arel using Product.name_a.arel, but now you have to figure out how to merge the conditions.

Combine multiple scope or where queries with OR

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.

How to combine two scopes with OR

Answering my own question. I think I figured out a way.

where("pages.id IN (?) OR pages.id IN (?)",
  Page.where(
      "user_id IN (?) OR user_id = ?",
      user.watched_ids, user.id
  ),
  Page
    .joins(:attachments)
    .where("attachments.tag_id IN (?)", user.tags)
    .select("DISTINCT pages.*")
)

It seems to be working so far, hope this is it!

How to combine scopes in Ruby on Rails 3?

In SQL, you can search by multiple conditions. You have defined two scoped, and they can be chained together as follows:

Project.search_by_name('fooproject').search_by_client('misterx')

This creates the following SQL:

SELECT "projects".* FROM "projects" WHERE "projects".name LIKE '%fooproject%' AND "projects".client_id LIKE '%misterx'

The operator joining these two conditions is "AND" which means that the result would be those that meet both conditions, not either condition.

There are a few ways to retrieve projects having a certain name, or projects belonging to a client. The most simple way is to create a new scope which specifies the OR operator:

scope :search_by_name_or_client, lambda { |name, client| where('name LIKE ? OR client_id LIKE ?', "%#{name}%", "%#{client}%") }

You may also want to look at SQL UNIONs, which combines the result set of two or more select statements. ActiveRecord does not handle UNION functionality, but there are gems to extend the functionality to include this, such as https://github.com/tsmango/union

And example of writing this using the Union gem would look like this:

Project.union([{:conditions => ['name like ?', "%#{name}%"]}, {:conditions => ['client like ?', "%#{client}%"]}])

This would generate the following SQL:

SELECT "projects".* FROM "projects" WHERE "projects".name LIKE '%fooproject%'
UNION
SELECT "projects".* FROM "projects" WHERE "projects".client_id LIKE '%misterx'

More information on SQL UNIONs can be found here: http://www.w3schools.com/sql/sql_union.asp

Using OR with queries in a scope

You have Squeel to your rescue. More details here.

Using that, you could define something like:

class Teacher
  ...
  scope :active_teachers, joins{subjects}.where {(active == true) | (subjects.active == true)}
  ...
end

Rails 3 merging scopes with joins

Apparently, at this time you can only merge simple constructs that don't involve joins. Here is a possible workaround if you modify your models to look like this:

class SolarSystem < ActiveRecord::Base
  has_many :planets
  has_many :planet_types, :through => :planets

  scope :has_earthlike_planet, joins(:planet_types).merge(PlanetType.like_earth)
end

class Planet < ActiveRecord::Base
  belongs_to :solar_system
  belongs_to :planet_type

  scope :like_earth, joins(:planet_type).merge(PlanetType.like_earth)
end

class PlanetType < ActiveRecord::Base
   has_many :planets

   attr_accessible :gravity, :life

   scope :like_earth, where(:life => true, :gravity => 9.8)
end

** UPDATE **

For the record, a bug was filed about this behavior - hopefully will be fixed soon...

Rails 3: How to merge queries or scopes for complex query?

Try using scopes:

class Event < AR::Base
  scope :active, lambda { |date| where("start_date < ? AND end_date > ?", date) }
  scope :future, lambda { |date| where("end_date < ?", date }
  ...
end

# Console
> @active_events = Event.active(Date.today)
> @future_events = Event.future(Date.today)

See http://guides.rubyonrails.org/active_record_querying.html

Rails 5 joining activerecord scopes with OR

A scope represents a narrowing of a database query, such as where(color: :red).select('shirts.*').includes(:washing_instructions) (https://api.rubyonrails.org/classes/ActiveRecord/Scoping/Named/ClassMethods.html#method-i-scope)

Joining scopes using OR condition is not possible. A scope narrows resultset with some filter. Chaining scopes together is equivalent to using scope filters at the same time, ie SQL's AND condition.

For your case when 2 scopes need to be joined with OR you could merge their resultsets:

Vehicle.unassigned.to_a | Vehicle.driverless.to_a

but it's better to write another scope, as you already did.

---

Related Topics