Querying Flask-SQLAlchemy through two table joins

Question

I've got three tables (for a sport-related application): Rounds, Games and Leagues.

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.

In my Models Rounds have many Games and Rounds have a League but there is no direct relationship between Games and Leagues.

Here's my models simplified:

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

How do I query Games with a round.league condition?

I'm thinking something like this, but it isn't working:

game = Game.query.join(Round).join(League).filter(
        Game.utc_time < datetime.utcnow(),
        League.id == league.id
    ).order_by(Game.utc_time.desc()).first()

Answer 1

After pouring over the SQLAlchemy documentation for many hours the solution was simply that I needed to be more explicit when defining my joins. I suppose the table joins weren't obvious to SQLAlchemy for some reason.

Instead of just join(League) I had to tell it where to join join(League, Round.league_id == League.id)

The query ended up looking like this:

game = Game.query.join(Round).join(
    League, Round.league_id == League.id
).filter(
    Game.utc_time < datetime.utcnow(),
    League.id == league.id
).order_by(Game.utc_time.desc()).first()