ActiveRecord: How to do an 'OR' merge?

Question

I'm trying to decouple ActiveRecord queries in a model so they are reusable in different circumstances. To keep it simple, say I have a model called Product:

class Product < ActiveRecord::Base
  def self.over_stocked
    where('stock_count >= ?', 20)
  end

  def self.expensive
    where('price >= ?', 100.0)
  end
end

If I wanted to create a new method to find products that have too much stock AND are expensive, I could merge the two queries:

...
def self.costly_stock
  # SQL => '... WHERE stock_count >= 20 AND price >= 100.0'
  over_stocked.merge(expensive)
end

However how can I use these two methods to create a new query for products that are either expensive OR are over stocked? E.g:

...
def expensive_or_over_stocked
  # SQL => '... WHERE stock_count >= 20 OR price >= 100.0'
  ...
end

Basically I'm looking for something like merge that uses OR rather than AND. Ideally the solution would return an ActiveRecord Relation and not an Array. Obviously I could rewrite the query with where('stock_count >= ? OR price >= ?', 20, 100.0) however that wouldn't be very DRY

I don't think there's an easy answer to this one. I'd love someone to prove me wrong though. — sevenseacat, May 08 '14 at 02:35
@Pete - see this question here: http://stackoverflow.com/questions/3639656/activerecord-or-query — Master_Yoda, May 08 '14 at 02:37
@Master_Yoda thanks, I saw that question, though unfortunately it doesn't address my goal of having reusable and decoupled queries that can be merged either with an AND or OR. I also do don't think dropping down into Arel is an elegant solution — Pete, May 08 '14 at 02:43
I agree, but note that the most liked answer in the community in a really popular question is your non-DRY approach. It seems like AR doesn't really support what you want here. — Master_Yoda, May 08 '14 at 02:46
You could try this [plugin](https://github.com/woahdae/active_record_or). Came across it in the linked answer, havent tried it — Karthik T, May 08 '14 at 02:56
This might help you http://stackoverflow.com/questions/1482940/combine-two-named-scopes-with-or-instead-of-and — MayankS, May 08 '14 at 08:12

score 0 · Answer 1 · answered May 09 '14 at 12:20

I came up with the following solution. One can argue how DRY it is.

class Product < ActiveRecord::Base
  scope :over_stocked, -> { where.not(stock_count: [0..19]) }
  scope :expensive, -> { where.not(price: [0..99]) }
  scope :costly_stock, -> { expensive.over_stocked }
  scope :neither_expensive_nor_over_stocked, -> { where(stock_count: [0..19]).where(price: [0..99]) }

  def self.expensive_or_over_stocked
    Product.where.not(id: Product.neither_expensive_nor_over_stocked.pluck(:id))
  end
end

If you use`select` instead of `pluck` it should do one query, by way of a sub-query, instead of two. — Kris, Nov 27 '20 at 15:11

ActiveRecord: How to do an 'OR' merge?

1 Answers1