# ActiveRecord SQL search with a filter based on parameters

For work, my colleague and I sketched this method up as a brain dump of how we want this method to work. What I am trying to figure out is how can we break this method into smaller components so that it is easier to test to write tests for this. I am trying to avoid putting this into methods where they are coupled by parameters.

def self.for_venue(venue, options = {})
filter = options[:filter]

date_conditions = case options[:date]
when 'upcoming' then  'e.datetime > :datetime'
when 'past'     then  'e.datetime < :datetime'
end

sql_query = [
"SELECT #{search_fields}",
"FROM events e #{search_joins}",
"WHERE " +  [date_conditions, "e.venue_id = #{venue.id}", "e.deleted = 0"].reject(&:nil?).join(" AND ")
]
if options[:limit].present?
sql_query << "LIMIT #{options[:limit]}"
sql_query << "OFFSET #{options[:offset]}" if options[:offset].present?
end
sql_query << "ORDER BY #{options[:order]}" if options[:order].present?

sql = ActiveRecord::Base.prepare_sql([sql_query.join(" "), { :datetime => Time.now.beginning_of_day }])
results = SlaveDB.connection.select_all(sql)

filter ? filter.select(results) : results
end

• What's SlaveDB? why are you doing sql by hand instead of using AR? – tokland Jun 28 '13 at 16:22
• @tokland SlaveDB is a custom class in the project that handles the our Slave. Sometimes there is no need to load data into Active Record when you are doing a read. It's a lot faster and less memory consumption. – ericraio Jun 29 '13 at 22:57

Even if you don't want to load your data into ActiveRecord, you can use AR to prepare your SQL statement using to_sql:

def self.for_venue(venue, options = {})

filter = options[:filter]

date_conditions = case options[:date]
when 'upcoming' then  "datetime > ?"
when 'past'     then  "datetime < ?"
end

sql_query = Event
.where(:venue_id => venue.id, :deleted => 0)
.where(date_conditions, Time.now.beginning_of_day)
.joins(search_joins)
.limit(options[:limit])
.offset(options[:offset])
.order(options[:order])
.to_sql

results = SlaveDB.connection.select_all(sql_query)

filter ? filter.select(results) : results

end

• That is definitely cleaner, our app is still on rails 2.1 unfortunately. :( – ericraio Jul 3 '13 at 19:09