erockenjew.myopenid.com

[Ruby] On Merging two sets of conditions

2007-10-16T04:02:23+00:00

BTW, i know switching to ruby-sequel isn't actually an acceptable solution, just wanted to point out how the :conditions => in ActiveRecord::Base.find is not the best implementation of building SQL statements.

[Ruby] On Merging two sets of conditions

2007-10-16T04:00:42+00:00

Or you could just switch to using ruby-sequel instead of ActiveRecord. ruby-sequel defines a flued interface for building queries, and queries are 'lazy-loaded' which means they don't get executed until they need to be (such as when you call .each)

DB[:users].filter(:country => "US").or("confirmed = ?", 1).filter("ids NOT IN (?)", [1, 10, 100])
#=> SELECT * FROM users WHERE country = 'US' OR confirmed = 1 AND ids NOT IN (1,10,100)

[Ruby] On Merging two sets of conditions

2007-10-16T02:44:17+00:00

I wrote an extremely small class to deal with this same problem, which makes use of ActiveRecord::Base.sanitize_sql, and makes it a little more apparent in the code what is happening. Its also really easy to test.

# used for building a conditions string for an ActiveRecord find
# 
# usage:
#   
#   conditions = Conditioner.new
#   conditions << ['open = ? AND user_id = ?', 1, @user.id]
#   conditions << ['search_term LIKE ? ', "%#{params[:search_term]}"]
#   
#   Opportunity.find(:all, :conditions => conditions.to_conditions)
#   # select * from opportunity WHERE open = 1 AND user_id = 1 AND search_term LIKE '%hello%'
class Conditioner
  
  attr_reader :conditions 

  def initialize
    @conditions = []
  end
  
  # adds an array to the conditioner
  #
  # usage:
  #
  #   conditions = Conditioner.new
  #   conditions << ['open = ? AND user_id = ?', 1, @user.id]
  #
  def <<(con)
    @conditions << ActiveRecord::Base.send(:sanitize_sql_array, con)
  end
  
  alias_method :add, :<<
  
  
  # outputs the conditions in a string
  #
  # params:
  #
  #  * seperator (choose either AND or OR, defaults to AND)
  def to_conditions(seperator="AND")
    @conditions.join(" #{seperator} ")
  end
  
  
end