Databases
StackOverflow2013
Postsdbo
POnamed_scope for sum of column in related table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POnamed_scope for sum of column in related table
    primarykey
    data
    text
    <p>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.</p> <p>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</p> <p>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.</p> <p>Here is the Actor model as I have it currently:</p> <pre><code>class Actor &lt; ActiveRecord::Base has_many :acts, :dependent =&gt; :destroy has_many :decisions, :through =&gt; :acts, :order =&gt; 'created_at' named_scope :high_scores, { :conditions =&gt; {:finished =&gt; true}, :joins =&gt; "INNER JOIN acts ON actor.id = acts.actor_id INNER JOIN decisions on decision.id = decision.act_id", :group =&gt; 'actor.id', :select =&gt;'actors.*, SUM(acts.decisions.point_value) AS score', :order =&gt; "score DESC", :limit =&gt; 10 } end </code></pre> <p>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.</p> <p>*Edit: Updated named_scope to what I currently have</p> <p>This now gives me the following error:</p> <blockquote> <p>Mysql::Error: Unknown column 'acts.decisions.point_value' in 'field list': SELECT actors.*, SUM(acts.decisions.point_value) AS score FROM <code>actors</code> INNER JOIN acts ON actor.id = acts.actor_id INNER JOIN decisions on decision.id = decision.act_id WHERE (<code>actors</code>.<code>finished</code> = 1) GROUP BY actor.id ORDER BY score DESC LIMIT 10</p> </blockquote>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJohn
    1. USJohn
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POnamed_scope for sum of column in related table
      1. This table or related slice is empty.
      1. VTUpMod
    1. COi would suggest though that if you're going to have a really complex sql statement, why not just use execute statement then tie it to a class function? it's the same anyways, right? just a thought
      singulars
      1. POnamed_scope for sum of column in related table
      1. UScorroded
    2. COyou'd be correct in that it would work equally well (and maybe better since the abstraction layer of ActiveRecord wouldn't be there to get in the way), problem being complex SQL statements are nowhere near my ability. That's why I was hoping there'd be something Rails-y that would hold my hand. I'll dig in and see if I can sort out a straight-up SQL statement that'll get me where I need to be in the meantime.
      singulars
      1. POnamed_scope for sum of column in related table
      1. USJohn
    3. COmaybe you can ask for that particular statement here in SO? i am not a SQL guru myself and i actually thought that a complex statement like that can easily be turned into a real sql statement. anyway that's what my boss usually does(he's also a coder) and i do believe that it's a far more efficient way(since joins tend to slow down..doubly so with the rails wrapper)
      singulars
      1. POnamed_scope for sum of column in related table
      1. UScorroded
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload