title: SQLAlchemy filter query by related object
body: <p>Using <a href="http://en.wikipedia.org/wiki/SQLAlchemy" rel="noreferrer">SQLAlchemy</a>, I have a one to many relation with two tables - users and scores. I am trying to query the top 10 users sorted by their aggregate score over the past X amount of days.</p>
  
  <pre><code>users:  
    id  
    user_name  
    score  
  
  scores:  
    user   
    score_amount  
    created  
  </code></pre>
  
  <p>My current query is:</p>
  
  <pre><code> top_users = DBSession.query(User).options(eagerload('scores')).filter_by(User.scores.created &gt; somedate).order_by(func.sum(User.scores).desc()).all()  
  </code></pre>
  
  <p>I know this is clearly not correct, it's just my best guess. However, after looking at the documentation and googling I cannot find an answer. </p>
  
  <p>EDIT:
  Perhaps it would help if I sketched what the <a href="http://en.wikipedia.org/wiki/MySQL" rel="noreferrer">MySQL</a> query would look like:</p>
  
  <pre><code>SELECT user.*, SUM(scores.amount) as score_increase 
  FROM user LEFT JOIN scores ON scores.user_id = user.user_id 
  WITH scores.created_at &gt; someday 
  ORDER BY score_increase DESC
  </code></pre>