Rails, working with named_scope

Question

Hello,

I need to select some dynamic price ranges submitted from a search form. How should I approach this with scopes? I am looking for something like this

Painting.price_range(['1..500', '2000..5000'])

SELECT * FROM paintings WHERE price BETWEEN 1..500 **OR** BETWEEN 2000..5000 etc.

Best regards. Asbjørn Morell.

Answer 1

named_scope :price_range, :conditions => ["(price BETWEEN 1 AND 500) OR (price BETWEEN 2000 AND 5000)"]

OR

named_scope :price_range, :conditions => ["(price ?) OR (price ?)", (1..500).to_s(:db), (2000..5000).to_s(:db)]

Dynamic

named_scope :price_between, lambda { |from, to| { :conditions => ['price > ? AND price <= ?', from, to] } }

named_scope :price_between, lambda { |from, to| { :conditions => ['price BETWEEN ? AND ?', from, to] } }

->

MyModel.price_between(1,100)

Answer 2

You'll need to use a lambda on the named_scope. The following should work:

  named_scope :price_range, lambda { |ranges|
    { 
      :conditions => ["(" + 
         ranges.collect {"price between ? and ?"}.join(" or ") + 
                      ")"] +
         ranges.collect {|r| [r.min, r.max]}.flatten 
    }
  }

The first ranges.collect creates as many "between ? and ?" checks as you have ranges and then the second ranges.collect flattens out the ranges and adds them as values to be sanitized into the conditions. I've stuck brackets round the ors just to be on the safe side.