CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
user2719905
user2719905

Reputation: 33

SQLAlchemy - How to query 3 tables (with association table)

I have 3 tables (SQLAlchemy classes): Team, Game, and GameObserver. Each game has 2 playing teams (offense & defense) but many observing teams.

What query would return all observers (teams) of games where the offensive team email was [email protected]?

class Team(db.Model):
    id = db.Column(db.Integer(), primary_key=True)
    email = db.Column(db.String(120), index=True, unique=True)

class Game(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    offense_id = db.Column(db.Integer(), db.ForeignKey('team.id'))
    defense_id = db.Column(db.Integer(), db.ForeignKey('team.id'))
    offense = db.relationship('Team', 
        foreign_keys=[offense_id], backref='offensive_games')
    defense = db.relationship('Team', 
        foreign_keys=[defense_id], backref='defensive_games')

class GameObserver(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    observer_id = db.Column(db.Integer(), db.ForeignKey('team.id'))
    game_id = db.Column(db.Integer(), db.ForeignKey('game.id'))
    game = db.relationship('Game', 
        foreign_keys=[game_id], backref='game_watchers')
    observer = db.relationship('Team', 
        foreign_keys=[observer_id], backref='watched_games')

One solution that I found works is:

db.session.query(Game.id, Team.email).filter(GameObserver.observer_id == 
    Team.id).filter(GameObserver.game_id == Game.id).filter(Game.offense.has(email = 
        '[email protected]')).all()

However, I suspect there is a better way to do this using joins. Does anyone have a better approach?

Upvotes: 3

Views: 1973

Answers (1)

Brendan Abel
Brendan Abel

Reputation: 37509

This should return a row for each GameObserver record that matches your criteria.

from sqlalchemy.orm import aliased

off_Team = aliased(Team)
obs_Team = aliased(Team)

results = db.session.query(obs_Team.email, Game.id)\
    .select_from(GameObserver)\
    .join(Game)\
    .join(obs_Team, GameObserver.observer)\
    .join(off_Team, Game.offense)\
    .filter(off_Team.email == '[email protected]')\
    .all()

Upvotes: 2

Related Questions