ActiveRecord SQL search with a filter based on parameters

Question

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 

Answer 1

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