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

What's SlaveDB? why are you doing sql by hand instead of using AR? — tokland
– tokland, Commented Jun 28, 2013 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
– ericraio, Commented Jun 29, 2013 at 22:57

kardeiz · Accepted Answer · 2013-07-02 18:27:03Z

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

answered Jul 2, 2013 at 18:27

kardeiz

5714 silver badges10 bronze badges

\$\begingroup\$ That is definitely cleaner, our app is still on rails 2.1 unfortunately. :( \$\endgroup\$

ericraio
– ericraio

2013-07-03 19:09:38 +00:00
Commented Jul 3, 2013 at 19:09

Add a comment |

Stack Exchange Network

ActiveRecord SQL search with a filter based on parameters

1 Answer 1

You must log in to answer this question.

Hot Network Questions

ActiveRecord SQL search with a filter based on parameters

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions