3

Rails 2.3.5

I've looked at a number of other questions relating to building conditions dynamically for an ActiveRecord find.

I'm aware there are some great gems out there like search logic and that this is better in Rails3. However, I'm using geokit for geospacial search and I'm trying to build just a standard conditions set that will allow me to combine a slew of different filters.

I have 12 different filters that I'm trying to combine dynamically for an advanced search. I need to be able to mix equality, greater than, less than, in (?) and IS NULLs conditions.

Here's an example of what I'm trying to get working:

conditions = []
conditions << ["sites.site_type in (?)", params[:site_categories]] if params[:site_categories]
conditions << [<< ["sites.operational_status = ?", 'operational'] if params[:oponly] == 1
condition_set = [conditions.map{|c| c[0] }.join(" AND "), *conditions.map{|c| c[1..-1] }.flatten]


@sites = Site.find :all,
                     :origin => [lat,lng],
                     :units => distance_unit,
                     :limit => limit,
                     :within => range,
                     :include => [:chargers, :site_reports, :networks],
                     :conditions => condition_set,
                     :order => 'distance asc'

I seem to be able to get this working fine when there are only single variables for the conditions expression but when I have something that is a (?) and has an array of values I'm getting an error for the wrong number of bind conditions. The way I'm joining and flattening the conditions (based on the answer from Combine arrays of conditions in Rails) seems not to handle an array properly and I don't understand the flattening logic enough to track down the issue.

So let's say I have 3 values in params[:site_categories] I'll the above code leaves me with the following:

Conditions is 

[["sites.operational_status = ?", "operational"], ["sites.site_type in (?)", ["shopping", "food", "lodging"]]]

The flattened attempt is:

["sites.operational_status = ? AND sites.site_type in (?)", ["operational"], [["shopping", "food", "lodging"]]]

Which gives me:

wrong number of bind variables (4 for 2)

I'm going to step back and work on converting all of this to named scopes but I'd really like to understand how to get this working this way.

Community
  • 1
  • 1
Nick
  • 8,483
  • 10
  • 46
  • 65
  • IMHO, i do think that your named scopes idea or even separating them into single methods is better than creating a search string dynamically. named scopes(or methods) make your model(and method) calls much much easier to read, while trying to mash up a query leads to a lot of pain and introduces a lot of possible problems. let rails do the querying for you(unless you need them totally optimized i guess) – corroded Aug 17 '11 at 18:45
  • @corroded - I'm not understanding how to combine 10-15 named scopes dynamically and generate a single query. I want to avoid hitting the database again and again for a single set of conditions and I don't know how many conditions will be specified in a given search. – Nick Aug 17 '11 at 19:45

1 Answers1

3

Rails 4

users  = User.all
users  = User.where(id: params[id]) if params[id].present?
users  = User.where(state: states) if states.present?
users.each do |u|
  puts u.name
end

Old answer

Monkey patch the Array class. Create a file called monkey_patch.rb in config/initializers directory.

class Array
  def where(*args)
    sql = args.first    
    unless (sql.is_a?(String) and sql.present?)
      return self
    end    
    self[0] = self.first.present? ? " #{self.first} AND #{sql}  " : sql 
    self.concat(args[1..-1])    
  end
end

Now you can do this:

cond = []
cond.where("id = ?", params[id]) if params[id].present?
cond.where("state IN (?)", states) unless states.empty?
User.all(:conditions => cond)
Harish Shetty
  • 64,083
  • 21
  • 152
  • 198