CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3ruby-on-rails-4
Prabhakaran
Prabhakaran

Reputation: 4013

rails 4 multiple range between conditions

I am having the following params in my url.

Consider url../price=0-1000_2000-5001

I have converted the above price params as 

pri = [["0", "1000"], ["2000", "5001"]]  #This is not limited to only two

Now how can I query as following in rails

select * from product where price betweeen 0 and 1000 and price between 2000 and 5001 and so on

I found below query in rails

Product.where(price: pri[0].first..pri[0].last)

But how can I find for multiple ranges.

Edit-1

@posts = Post.where(price: price_range) #This works fine now

[1001..5000, 10000..0]

Here 10000..0 Indicate price greater than 10000

But my query executed as

select * from posts where price between 1001 and 5000 or price between 10000 and 0

But the query should be like this

select * from posts where price between 1001 and 5000 or price > 10000

checkbox1 = value 0-1000
checkbox2 = value 1001-5000
checkbox3 = value 5001-10000
checkbox4 = value 10001-15000
checkbox5 = value 15001-0(This indicates price > 15001 )

Upvotes: 0

Views: 2419

Answers (4)

Mike T
Mike T

Reputation: 2636

There is a much simpler solution. This is all you need:

price_ranges = [0..1000, 2000..5001]
products = Product.where(price: price_ranges)

Here's console output, with a similar query, showing the SQL that's generated:

>> ranges
=> [-Infinity..1000000, 10000000..19999999]
>> Company.where(total_funding_amount: ranges)
Company Load (0.8ms)  SELECT "companies".* FROM "companies"  WHERE ((("companies"."total_funding_amount" <= 1000000 OR "companies"."total_funding_amount" BETWEEN 10000000 AND 19999999) OR 1=0))

You can even use a mix of ranges and integers. See the discussion in this Rails issue from 2011 if you're curious.

Upvotes: 0

pdobb
pdobb

Reputation: 18037

Here's a solution that uses Range objects instead of a manual representation of ranges. And ARel instead of hardcoded SQL. It's generally good practice to use ARel over SQL strings so that ARel can handle formatting the SQL for whatever database adapter you may be using now and in the future.

price_ranges = [0..1000, 2000..5001]

# Set up the base relation
products = Product.where(price: price_ranges.pop)

# iterate on any remaining prices
products = price_ranges.inject(products) do |rel, price_range|
  # Use ARel's `or` method to chain on to the relation
  rel.or(Product.arel_table[:price].in(price_range))
end if price_ranges.any?

If you need to, you can first convert your price arrays into ranges like this:

price_ranges = prices.map { |ar| Range.new(Integer(ar.first), Integer(ar.second)) }

Update:

If you want to continue to use ranges but allow for one value to be anything greater than some number you can use Float::INFINITY to accomplish this:

price_ranges = [1001..5000, 10000..Float::INFINITY]

# For example:
Product.where(price: 10000..Float::INFINITY).to_sql
  # => SELECT "products".* FROM "products"  WHERE ("products"."id" >= 10000)

Of course, this probably means you'll need to conditionally set the 2nd value to Float::INFINITY when building up the array of ranges.

Upvotes: 1

nishanthan
nishanthan

Reputation: 460

Instead of using AND between two range use OR this must return some products 

Product.where("price between ? and ? or price between ? and ?", pri[0].first, pri[0].last, pri[1].first, pri[1].last)

Edited for dynamic array lenght

arr = [["0", "100"], ["100", "200"]]
query_string = ""
arr.each {|a| query_string << (arr.last == a ? " price between #{a.first} and #{a.last}"     : "price between #{a.first} and #{a.last} or") }
Product.where(query_string)

finds the expected records.

Upvotes: 0

Mikhail Nikalyukin
Mikhail Nikalyukin

Reputation: 11967

Product.where("(price >= ? AND price <= ?) OR (price >= ? AND price <= ?)", pri[0].first, pri[0].last, pri[1].first, pri[1].last)

Something like this should work, i didn't test this code though.

Upvotes: 0

Related Questions