Rails Reporting, Raw SQL, and ActiveRecord

Most of the time when you dump some data out using Rails you are only dumping out a page of data at a time into an XHTML representaton. This is often done with some kind of pagination and it all works pretty well and speed doesn't become an issue. When doing reporting, however, you may be providing a much larger number of records to the user in the form of an Excel document or CSV file that the user will use third party software to do number crunching on. In this case the process of Rails reconstituting all of those model objects can really slow things down and you will likely need to resort to writing raw SQL and avoiding ActiveRecord. You don't, however, have to abandon ActiveRecord completely as it has some very helpful methods for constructing the SQL queries you will issue. Some of these methods are private but you can invoke them using the send method. Here is an example that uses ActiveRecord to append where and order clauses to the manually constructed sql statement:

	sql = 'a big honking sql statement'
	conditions = conditions_for_doodad
	order = "#{params[:order_by]} #{params[:order]}"
	Doodad.send(:add_conditions!, sql, conditions)
	Doodad.send(:add_order!, sql, order)
	@rs = ActiveRecord::Base.connection.select_all(sql)

view raw gistfile1.rb hosted with ❤ by GitHub

A few notes...When you call select_all you are returned an array of hashes with the column names as keys. If you call execute you will get an array of arrays and you will have to use column positions to index into the array (probably a bit faster than select_all on producing that record set). As of Rails 2.0 the record set that select_all returns can be used as the :collection parameter in a partial. Lastly, in addition to the :add_conditions! and :add_order! methods there is a very handy :construct_finder_sql as well which accepts the same options your would pass to a regular finder.