title: SQLAlchemy: filter model object with deep relationships?
body: <p>I have three model objects with two one-to-many relationships:
  <code>Match &lt;-&gt; Slot &lt;-&gt; Player</code>
  Relationships are defined with backrefs <code>'player'</code> and <code>'match'</code>. So what I'm trying to do is find all matches that satisfy <code>match_filter</code> filter:</p>
  
  <pre><code>slot_filter = or_(Slot.account_id==None,
                    Slot.additional_units!=None)
                  # func.count(Slot.player.slots)&lt;=1)
  
  match_filter = and_(Match.human_players==10,
                      Match.game_mode==2,
                     ~Match.slots.any(slot_filter)) 
  
  query = session.query(Match).join(Slot)
  query = query.filter(match_filter)
  matches = query.all()
  </code></pre>
  
  <p>The problem is that I don't really know where should I add Player.slots in my query and how can I filter out matches in which at least one player has less (or equal) than n matches played. I already read something about <code>group_by</code>, <code>having</code>, and <code>func.count</code> methods, but I still don't understand how to use them in my case.</p>
  
  <p><strong>Edit</strong> I found almost the same question: <a href="https://stackoverflow.com/questions/25914494/filter-by-grandchildren-count-in-sqlalchemy">Filter by grandchildren count in SQLAlchemy</a>
  But I still have not figure out how to apply this to my relationships</p>
  
  <p><strong>Edit</strong> Here is my solution:</p>
  
  <pre><code>subquery = session.query(Player.id).\
             join(Slot).\
             group_by(Player.id).\
             having(func.count(Player.slots) &gt; 1).\
             subquery()
  
  matches =  session.query(Match).\
             join(Slot).\
             join(Player).\
             filter(Player.id.in_(subquery)).\
             filter(match_filter).all()
  </code></pre>