title: Querying Flask-SQLAlchemy through two table joins
body: <p>I've got three tables (for a sport-related application): Rounds, Games and Leagues.</p>
  
  <p>I want to find the most recent Round for a League. To do that I need find the most recent Game and find what Round it is in.</p>
  
  <p>In my Models Rounds have many Games and Rounds have a League but there is no direct relationship between Games and Leagues.</p>
  
  <p>Here's my models simplified:</p>
  
  <pre><code>class Round(db.Model):
      """Round Model."""
  
      __tablename__ = 'rounds'
  
      id = db.Column(db.Integer, primary_key=True)
      order = db.Column(db.Integer, nullable=False)
      league_id = db.Column(db.Integer, db.ForeignKey('leagues.id'))
  
      league = db.relationship('League', backref='rounds')
  
  
  class Game(db.Model):
      """Game model."""
  
      __tablename__ = "games"
  
      id = db.Column(db.Integer, primary_key=True)
      utc_time = db.Column(db.DateTime)
      round_id = db.Column(db.Integer, db.ForeignKey('rounds.id'))
  
      round = db.relationship('Round', backref="games")
  
  
  class League(db.Model):
      """League Model."""
  
      __tablename__ = 'leagues'
  
      id = db.Column(db.Integer, primary_key=True)
      name = db.Column(db.String, nullable=False)
  
      def __init__(self, name, abbreviation, other_names):
          self.name = name
  </code></pre>
  
  <p>How do I query Games with a round.league condition?</p>
  
  <p>I'm thinking something like this, but it isn't working:</p>
  
  <pre><code>game = Game.query.join(Round).join(League).filter(
          Game.utc_time &lt; datetime.utcnow(),
          League.id == league.id
      ).order_by(Game.utc_time.desc()).first()
  </code></pre>