Ruby on Rails - Using Float::INFINITY with where query, any drawbacks?

Question

So, I have a search method in my model as follows:

def self.advanced_search(name, min_experience, max_hourly_rate)
    where('lower(name) LIKE ? AND experience >= ? AND hourly_rate <= ?', "%#{name.downcase}%", min_experience, max_hourly_rate)
end

Now if the max_hourly_rate is blank then I'd get this error:

PG::InvalidTextRepresentation: ERROR:  invalid input syntax for type double precision: ""
LINE 1: ...IKE '%%' AND experience >= '5' AND hourly_rate <= '') AND (l...

Instead of using another boring if statement I set max_hourly_rate to infinity if it's blank

def self.advanced_search(name, min_experience, max_hourly_rate)
    max_hourly_rate = Float::INFINITY if max_hourly_rate.blank?
    where('lower(name) LIKE ? AND experience >= ? AND hourly_rate <= ?', "%#{name.downcase}%", min_experience, max_hourly_rate)
end

Does this method have any drawbacks? If yes, is there a better solution?

Answer 1

One obvious drawback is that every condition that you add makes your database query more complex and slower. Another drawback is that this approach doesn't work with all databases. It might work on PostgreSQL but SQLite for example would raise an SQLException.

I would just add several scopes and use them. Scopes are chainable and it is fine for a scope to have an empty condition.

scope :by_name, ->(name) { where('lower(name) LIKE ?', "%#{name.downcase}%") if name.present? }
scope :min_experience, ->(exp) { where('experience >= ?', exp) if exp.present? }
scope :max_hourly_rate, ->(rate) { where('hourly_rate <= ?', rate.present?) if rate }

With scopes like that, you can write your query like this and do not have to care about nil values

self.advanced_search(name, min_exp, max_rate)
  by_name(name).min_experience(min_exp).max_hourly_rate(max_rate)
end