From ActiveRecord to Raw SQL
ActiveRecord is awesome. Its dynamic finder methods, scopes, and effortless joins make working with a database a snap. But there are always those times when writing raw sql makes more sense. In my case, I was joining across several tables, and then sorting by a value from the joined table. The problem was that my views were written to handle an ActiveRecord object, not a simple hash, which is what a find_by_sql query returns.
Before:
#controller:
@posts = Post.find(:all)
#view:
< % @posts.each do |post| %>
< %= post.title %>
< %= post.body %>
< %= post.author %>
< %= post.date %>
< % end %>
After:
#controller:
@posts = ActiveRecord::Base.connection.execute("SELECT * FROM posts")
# @posts.class #=> Hash
# @posts[0].title will return a method missing error
For the view you have two choices.
1)Rewrite the method calls:
< % @posts.each do |post| %>
< %= post['title'] %>
< %= post['body'] %>
< %= post['author'] %>
< %= post['date'] %>
< % end %>
#this is tedious
or
2) Hack the hash to make keys act as methods. There are several ways to do this. From a
stackoverflow question I asked, someone showed me a module in the Ruby standard library called OpenStruct:
#controller
require 'ostruct'
@posts = OpenStruct.new(ActiveRecord::Base.connection.execute("SELECT * FROM posts"))
#view
< % @posts.each do |post| %>
< %= post.title %>
< %= post.body %>
< %= post.author %>
< %= post.date %>
< % end %>
Other ways to do this (from stackoverflow answerers):
- define the method_missing function for the
Hash class
- define the method_missing function for just the hash instance you're dealing with
You can try these out with a really cool
pastebin that actually runs the code:
It's interesting that all of these solutions used about 5mb of memory according to the
ideone.com.
Alan
19 November 2009