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()

• TheRealChx101: So no need for the foreign key mapping then I guess
• KevinCK: From the query object can i create a schema for it, I want the result to get a dictionary as the final output?

Answer 2

You are missing a relationship between League and Round.

class League(db.Model):
 (...)
 db.relationship("Round", backref='league')
 (...)

Your query should work after adding that to your League model.

• hammygoonan: Thanks @AArias. Annoyingly for everyone, I do have that relationship in there, just on the Round model (exactly the same only reversed). My apologies, I accidentally deleted that line when editing the question and have added it back in now.
• AArias: Mm. Did you try reversing both relations declarations? Try declaring the League-Round relationship in the league model (as in my code example) and the Round-Game one in the Round model. I can't try it right now cos I'm on my phone but at least in the sqlalchemy examples for one to many relationships the relationship is declared in the parent model: docs.sqlalchemy.org/en/latest/orm/… let me know if that works and I'll update the answer
• AArias: Also, leave league_id and round_id as they are.
• hammygoonan: Hi @AArias, I did. See my solution above - I'd been meaning to update this for a couple of days now. I tried your suggestions but ultimately my solution is what got me over the line.