Multiple query conditions in Rails - if they exist.

Question

I found a few similar questions while searching here, but when I tried to add unless to the solutions I found, things started to break...

Here's what I have that works:

Controller:

    @metrics = Metric.where("current_ratio > ?", @screen.current_ratio_min) unless @screen.current_ratio_min.nil?

Once I add another .where line (of which I need to add many),

    @metrics = Metric.where("current_ratio > ?", @screen.current_ratio_min) unless @screen.current_ratio_min.nil?
    .where("current_ratio < ?", @screen.current_ratio_max) unless @screen.current_ratio_max.nil?

I get an error:

undefined method `where' for false:FalseClass

I'm assuming this is because the first unless is ending my query. How do I apply an unless just to each individual condition? If that is, in fact, the problem :\

Thanks in advance!

In the related section, I found http://stackoverflow.com/questions/9308820/rails-searching-with-multiple-conditions-if-values-are-not-empty?rq=1 which solves me problem I think... I guess if you don't know what to search, it makes it hard to find what you're looking for! — jon, Jul 08 '13 at 04:28

score 10 · Accepted Answer · answered Jul 08 '13 at 05:27

10

@metrics = Metric.all
@metrics = @metrics.where('current_ratio > ?', @screen.current_ration_min) if @screen.current_ratio_min.present?
@metrics = @metrics.where('other_value > ?', @screen.other_value) if @screen.other_value.present?

This is the best way I can think of without programmatically building a where clause string which can be risky for SQL injection.

Keep adding as many conditions as you want. Notable, use if something.present? instead of your unless something.nil?

Also, the Metric.all might not be ideal, but whatever you need to get all records to start with.

answered Jul 08 '13 at 05:27

Joel Friedlaender

2,191
1
18
24

3

This will only work in rails 4, in rails 3 all executes the query and returns an array (you could use `scoped` instead in rails 3) – Frederick Cheung Jul 08 '13 at 08:30
Thank you! This is much cleaner than what I was thinking to do. And looks to be more scalable to boot! Thanks to everyone, apparently this was a more complicated problem than I thought! Lots of great ideas :) – jon Jul 13 '13 at 21:11
In Rails4, How many SQL select will be executed ? 3 times ? or just one times? – Jiejing Zhang May 12 '14 at 02:58
Got my question's answer, rails will lazy evaluate, so it will be only one Database query. – Jiejing Zhang May 12 '14 at 03:01

score 1 · Answer 2 · answered Jul 08 '13 at 08:44

If you want clean code, use scope

In metric.rb

  scope :current_ratio_min, lambda {|current_ratio_min|
    current_ratio_min.present? ? where('current_ratio > ?', current_ration_min) : where()}
  scope :current_ratio_max, lambda {|current_ratio_max|
    current_ratio_max.present? ? where('current_ratio > ?', current_ratio_max) : where()}

Your query :

@metrics = Metric.current_ratio_min(@screen.current_ratio_min).current_ratio_max(@screen.current_ratio_max)`

score 0 · Answer 3 · answered Apr 07 '15 at 18:25

Write the following method in your Array class

 class Array
  def add_condition!(condition, conjunction = 'AND')
   if String === condition
     add_condition!([condition])
   elsif Hash === condition
     add_condition!([condition.keys.map { |attr| "#{attr}=?" }.join(' AND ')] + condition.values)
   elsif Array === condition
     unless condition.empty?
       self[0] = "(#{self[0]}) #{conjunction} (#{condition.shift})" unless empty?
       self.push(*condition)
     end
   else
    raise "don't know how to handle this condition type"
   end
  self
 end
end

You can build your conditions for ActiveRecord where or find with conditions as follows

 conditions = []
 conditions.add_condition!(["current_ratio > ?", @screen.current_ratio_min]) unless @screen.current_ratio_min.nil?  
 conditions.add_condition!(["current_ratio < ?", @screen.current_ratio_max]) unless @screen.current_ratio_max.nil?
 @metrics = Metric.where(conditions)

This will be helpful in building multiple conditions with AND/OR combinations

t56k · Answer 4 · 2013-07-08T04:31:39.250

-1

What about something like this?

if !@screen.current_ratio_min.nil? && !@screen.current_ratio_max.nil?
  @metrics = Metric.where("current_ratio > ?", @screen.current_ratio_min).where("current_ratio < ?", @screen.current_ratio_max)
elsif @screen.current_ratio_min.nil? && !@screen.current_ratio_max.nil?
  @metrics = Metric.where("current_ratio < ?", @screen.current_ratio_max)
elsif !@screen.current_ratio_min.nil? && @screen.current_ratio_max.nil?
  @metrics = Metric.where("current_ratio > ?", @screen.current_ratio_min)
else
  @metrics = Metric.all
end

edited Jul 08 '13 at 04:31

answered Jul 08 '13 at 04:23

t56k

6,769
9
52
115

I wasn't as clear as I should have been. It is possible for someone to put in a min value, but leave max value blank. In which case, I just want to search greater than min value. If that makes sense. – jon Jul 08 '13 at 04:26
This is going to get very messy if he has a few optional filters. – Joel Friedlaender Jul 08 '13 at 05:27

score -1 · Answer 5 · answered Jul 08 '13 at 05:54

-1

Try with the following code

if @screen.current_ratio_min and @screen.current_ratio_max
  @metrics = Metric.where("current_ratio > ? and current_ratio < ?", @screen.current_ratio_min, @screen.current_ratio_max)
else
 unless @screen.current_ratio_min.blank?
   @metrics = Metric.where("current_ratio > ?", @screen.current_ratio_min)
 else
   unless @screen.current_ratio_max.blank?
     @metrics = Metric.where("current_ratio < ?", @screen.current_ratio_max)
   else
     @metrics = Metric.all
   end
 end 
end

answered Jul 08 '13 at 05:54

Bachan Smruty

5,686
1
18
23

1

Imagine how this code looks when you add one more optional parameter. – Joel Friedlaender Jul 08 '13 at 06:33
Joel, it is fetching the limited records as per the condition. If we will fetch all the records first and then we will go for applying conditions on that, that means for each condition, we need to fetch all the records first, which can be optimized in this way :) . – Bachan Smruty Jul 08 '13 at 06:37
It is true that my option isn't as query optimized, but this code would be unworkable with more parameters. If they need to optimize I think they need to programmatically build the query. I wouldn't prematurely optimize though at the cost of code maintainability. – Joel Friedlaender Jul 08 '13 at 06:42

Multiple query conditions in Rails - if they exist.

5 Answers5

Linked