CODEWITHSUNDEEP
ruby-on-railsactiverecordconditional-statementsmultiple-conditions
jon
jon

Reputation: 170

Multiple query conditions in Rails - if they exist.

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!

Upvotes: 7

Views: 7246

Answers (5)

Gowtham
Gowtham

Reputation: 652

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

Upvotes: 0

user2503775
user2503775

Reputation: 4367

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)`

Upvotes: 1

Bachan Smruty
Bachan Smruty

Reputation: 5734

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

Upvotes: -1

Joel Friedlaender
Joel Friedlaender

Reputation: 2191

@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.

Upvotes: 10

t56k
t56k

Reputation: 6981

What about something like this?

if [email protected]_ratio_min.nil? && [email protected]_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? && [email protected]_ratio_max.nil?
  @metrics = Metric.where("current_ratio < ?", @screen.current_ratio_max)
elsif [email protected]_ratio_min.nil? && @screen.current_ratio_max.nil?
  @metrics = Metric.where("current_ratio > ?", @screen.current_ratio_min)
else
  @metrics = Metric.all
end

Upvotes: -1

Related Questions