How to chain scope queries with OR instead of AND

Question

I m using Rails3  ActiveRecord  Just wondering how can I chain the scopes with OR statements rather than AND   e g    Person where  name   gt   John   where  lastname   gt   Smith     That normally returns   name    John  AND lastname    Smith    but I d like     name    John  OR lastname    Smith

User · Answer

If you re looking to provide a scope  instead of explicitly working on the whole dataset  here s what you should do with Rails 5   scope  john or smith  - gt    where name   John   or where lastname   Smith        Or   def self john or smith   where name   John   or where lastname   Smith    end

User · Answer

Rails 4  scope  combined scope  - gt    where  name     or name        a    b

User · Answer

Use ARel  t   Person arel table  results   Person where    t  name  eq  John      or t  lastname  eq  Smith

User · Answer

An updated version of Rails ActiveRecord may support this syntax natively  It would look similar to   Foo where foo   bar   or where bar   bar     As noted in this pull request https   github com rails rails pull 9052  For now  simply sticking with the following works great   Foo where  foo    OR bar       bar    bar

User · Answer

If you can t write out the where clause manually to include the  or  statement  ie  you want to combine two scopes   you can use union   Model find by sql    Model scope1 to sql  UNION   Model scope2 to sql       source  ActiveRecord Query Union   This is will return all records matching either query  However  this returns an array  not an arel  If you really want to return an arel  you checkout this gist  https   gist github com j-mcnally 250eaaceef234dd8971b    This will do the job  as long as you don t mind monkey patching rails

User · Answer

You can also use MetaWhere gem to not mix up your code with SQL stuff   Person where   name   gt   John       lastname   gt   Smith

User · Answer

In case anyone is looking for an updated answer to this one  it looks like there is an existing pull request to get this into Rails  https   github com rails rails pull 9052   Thanks to  j-mcnally s monkey patch for ActiveRecord  https   gist github com j-mcnally 250eaaceef234dd8971b  you can do the following   Person where name   John   or where last name   Smith   all   Even more valuable is the ability to chain scopes with OR   scope  first or last name  - gt  name    where name  name split      first  or where last name  name split      last    scope  parent last name  - gt  name    includes  parents  where last name  name      Then you can find all Persons with first or last name or whose parent with last name  Person first or last name  John Smith   or parent last name  Smith     Not the best example for the use of this  but just trying to fit it with the question

User · Answer

So the answer to the original question  can you join scopes with  or  instead of  and  seems to be  no you can t   But you can hand code a completely different scope or query that does the job  or use a different framework from ActiveRecord e g  MetaWhere or Squeel  Not useful in my case  I m  or ing a scope generated by pg search  which does a bit more than select  it includes order by ASC  which makes a mess of a clean union  I want to  or  it with a handcrafted scope that does stuff I can t do in pg search  So I ve had to do it like this   Product find by sql     Product code starts with  Tom   to sql   union    Product name starts with  Tom   to sql       I e  turn the scopes into sql  put brackets around each one  union them together and then find by sql using the sql generated  It s a bit rubbish  but it does work   No  don t tell me I can use  against    name  code   in pg search  I d like to do it like that  but the  name  field is an hstore  which pg search can t handle yet  So the scope by name has to be hand crafted and then unioned with the pg search scope

User · Answer

the squeel gem provides an incredibly easy way to accomplish this  prior to this I used something like  coloradoblue s method    names     Kroger    Walmart    Target    Aldi   matching stores   Grocery where name like any names

User · Answer

For me  Rails 4 2 5  it only works like this     where  name     or name       a  b

User · Answer

Just posting the Array syntax for same column OR queries to help peeps out   Person where name    John    Steve

User · Answer

This is a very convenient way and it works fine in Rails 5   Transaction    where transaction type    Create    Correspond       or      Transaction where        transaction type   Status         field   Status         newvalue    resolved    deleted                or      Transaction where transaction type   Set   field   Queue

User · Answer

This would be a good candidate for MetaWhere if you re using Rails 3 0   but it doesn t work on Rails 3 1  You might want to try out squeel instead  It s made by the same author  Here s how d you d perform an OR based chain   Person where  name     John      lastname     Smith      You can mix and match AND OR  among many other awesome things

User · Answer

Also see these related questions  here  here and here  For rails 4  based on this article and this original answer  Person    unscoped   See the caution note below  Maybe you want default scope here  in which case just remove this line     where    Begin a where clause     where  name   gt   John   where  lastname   gt   Smith      join the scopes to be OR d      where values    get an array of arel where clause conditions based on the chain thus far      inject  or     inject the OR operator into the arels           Inject may not work in Rails3  But this should work instead       joins   OR             Remember to only use  inject or  joins  not both        Resurface the arels inside the overarching query   Note the article s caution at the end      Rails 4 1       Rails 4 1 treats default scope just as a regular scope  The default   scope  if you have any  is included in the where values result and   inject  or  will add or statement between the default scope and your   wheres  That s bad       To solve that  you just need to unscope the query

User · Answer

Rails 4   Scope   Arel  class Creature  lt  ActiveRecord  Base     scope  is good pet  - gt            where              arel table  is cat  eq true               or arel table  is dog  eq true                or arel table  eats children  eq false                   end   I tried chaining named scopes with  or and no luck  but this worked for finding anything with those booleans set  Generates SQL like  SELECT  CREATURES    FROM  CREATURES  WHERE     CREATURES   is cat    1 OR  CREATURES   is dog    1  OR  CREATURES   eats children    0

User · Answer

According to this pull request  Rails 5 now supports the following syntax for chaining queries   Post where id  1  or Post where id  2     There s also a backport of the functionality into Rails 4 2 via this gem

User · Answer

You would do  Person where  name   OR lastname      John    Smith     Right now  there isn t any other OR support by the new AR3 syntax  that is without using some 3rd party gem

User · Answer

Update for Rails4  requires no 3rd party gems  a   Person where name   John     or any scope  b   Person where lastname   Smith     or any scope  Person where  a  b  map  s  s arel constraints reduce  and    reduce  or       tap   sc  sc bind values    a  b  map  amp  bind values      Old answer  requires no 3rd party gems  Person where      Person where  name   gt   John   where  lastname   gt   Smith          where values reduce  or

User · Answer

names     tim    tom    bob    alex   sql string   names map    t   name      t      join   OR     people   People where sql string

[ruby-on-rails] How to chain scope queries with OR instead of AND?

Examples related to ruby-on-rails

Examples related to ruby-on-rails-3

Examples related to activerecord

Examples related to rails-activerecord