Using Select fields not part of models

Sometimes, you need to wirite a SQL query where the fields in the SELECT clause are not column names. For example that woul happen if youŕe selecting the result of a math formula such sum or average. It may also happen if you are joining 2 tables together and selecting columns from both tables.

If using the regular find function, the result set will only include fields of the Model used for the find call. The solution is to write the select_all method of the connection instance varable of the ActiveRecord::Base class. Example:

class Article < ActiveRecord::Base
  def self.find_article_plus_author(article_id)
    connection.select_all("SELECT a.title as title, 
                                  b.first_name as first_name                           
                           FROM articles a, authors b
                           WHERE a.author_id = b.id
                AND a.id=#{article_id}")
  end
end

The result is an array of hashes. There is 1 record in the array for each row returned by the SQL query. The hash keys arte strings with the names used in the select clause and the values, well, theyŕe the values found in the database.

art = Article.find_article_plus_author(12)
art.each do |a|
    puts "title = "+ a[0]["title"]
    puts "author = " + a[0]["first_name"]
end