CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
Ryan O.
Ryan O.

Reputation: 35

Flask SQLAlchemy: Child table with multiple parents?

I'm new to flask_sqlalchemy, and while I understand how the one-to-many, and many-to-many relationships work, I'm struggling to understand how I can apply them to my specific data types. I have the following three tables: TeamStat, PlayerStat, and Stat which are loosely described as follows

class PlayerStat(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    date = db.Column(db.DateTime, nullable=False, default=datetime.datetime)
    player_id = db.Column(db.Integer, db.ForeignKey('player.player_id'), nullable=False)

class TeamStat(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    date = db.Column(db.DateTime, nullable=False, default=datetime.datetime)
    team_id = db.Column(db.Integer, db.ForeignKey('team.team_id'), nullable=False)


class Stat(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    value = db.Column(db.String(80), nullable=False)

Since Stat is a generic table type, I would like to use it by both the PlayerStat table (for individual player stats), as well as the TeamStat table (as a sum of the stats for all players on a team). Can someone help me understand how I can refer one child table to multiple parent tables in this fashion?

Upvotes: 2

Views: 1670

Answers (1)

SuperShoot
SuperShoot

Reputation: 10861

Use association tables to bridge your stat table to your players and team tables. This example is pretty close to what you are already doing, except the date column is moved on to the stat record table and I've replaced your PlayerStat and TeamStat objects with unmapped tables.

I've assumed you have two ORM classes, Player and Team (not Flask-SQLAlchemy sorry but concept remains the same):

plr_stat_assc = Table('plr_stat_assc', Base.metadata,
    Column('player_id', Integer, ForeignKey('player.id')),
    Column('stat_id', Integer, ForeignKey('stat.id'))
)

team_stat_assc = Table('team_stat_assc', Base.metadata,
    Column('team_id', Integer, ForeignKey('team.id')),
    Column('stat_id', Integer, ForeignKey('stat.id'))
)

class Player(Base):
    __tablename__ = 'player'
    id = Column(Integer, primary_key=True)
    stats = relationship("Stat", secondary=plr_stat_assc)

class Team(Base):
    __tablename__ = 'team'
    id = Column(Integer, primary_key=True)
    stats = relationship("Stat", secondary=team_stat_assc)

class Stat(Base):
    __tablename__ = 'stat'
    id = Column(Integer, primary_key=True)
    date = Column(DateTime, nullable=False, default=datetime.datetime)
    value = Column(String(80), nullable=False)

Upvotes: 2

Related Questions