Rails: setting column alias attribute with find_by_sql

Question

When I use a column alias in a query with find_by_sql, it doesn't appear to be set in the result objects, even when I add an attr_accessor for the property.

class Country < ActiveRecord::Base

  attr_accessor :average_score

  def self.sorted_by_average_score
    sql = "SELECT country_id, AVG(score) AS average_score, countries.name " +
    "FROM players " +
    "INNER JOIN countries ON players.country_id = countries.id " +
    "GROUP BY country_id "
    Country.find_by_sql(sql)
  end
end

I would expect to be able to do this:

countries = Country.sorted_by_average_score.first.average_score

...but it always returns nil, even though a value is definitely returned from the query.

Can anyone explain to me why the attribute isn't set in the object?

Answer 1

Because attr_accessors have nothing to do with the way ActiveRecord treats your columns. I've shown it in this question. Basically all the stuff going on in finders that touches columns, works with the attributes hash, not with the instance variables that your accessors declare.

edit: actually my answer doesn't answer the question completely. it just explains why attr_accessor could not be of any help here. looking forward to see someone do the rest of the job :)

Answer 2

I would change this line:

Country.find_by_sql(sql)

into just this:

find_by_sql(sql)

and see if that helps.

attr_accessor is not necessary.

Answer 3

You don't need to use attr_accessor, see neutrino's explanation. You just need to access your virtual column by using the attributes hash. This question is the same as Rails: find_by_sql and virtual column. Sample code for your example:

countries = Country.sorted_by_average_score.first.attributes['average_score']