当前位置: 动力学知识库 > 问答 > 编程问答 >

ruby on rails - named_scope for sum of column in related table

问题描述:

My SQL skills are laughable at best, which is why Rails and ActiveRecord make my life a lot easier. This time, however, it probably represents a crutch that keeps me from understanding what's going on in the back end in order to know what to feed named_scope.

I'm trying to return the top 10 records of a table that represent the highest scoring "actors" in a game. Score is calculated via Actor>Acts>Decsisons.point_value

I can get the score for any individual actor easily enough, but the joins and sum are above my SQL understanding in order to scope it for the top 10 out of the whole db.

Here is the Actor model as I have it currently:

class Actor < ActiveRecord::Base

has_many :acts, :dependent => :destroy

has_many :decisions, :through => :acts, :order => 'created_at'

named_scope :high_scores, {

:conditions => {:finished => true},

:joins => "INNER JOIN acts ON actor.id = acts.actor_id INNER JOIN decisions on decision.id = decision.act_id",

:group => 'actor.id',

:select =>'actors.*, SUM(acts.decisions.point_value) AS score',

:order => "score DESC",

:limit => 10

}

end

After trying out a few things based on some posts here and on some other sites, I'm obviously not grasping what's going on or what named_scope needs to connect the dots. Some clarity would be much appreciated. Thanks.

*Edit: Updated named_scope to what I currently have

This now gives me the following error:

Mysql::Error: Unknown column 'acts.decisions.point_value' in 'field list': SELECT actors.*, SUM(acts.decisions.point_value) AS score FROM actors INNER JOIN acts ON actor.id = acts.actor_id INNER JOIN decisions on decision.id = decision.act_id WHERE (actors.finished = 1) GROUP BY actor.id ORDER BY score DESC LIMIT 10

网友答案:

Set final scores in a table column instead, as it seemed to be less resource intensive.

分享给朋友:
您可能感兴趣的文章:
随机阅读: