Correct way to implement many-to-many with additional columns in Flask-SQLAlchemy?

Viewed 47

I need to implement many-to-many relationship with additional columns in Flask-SQLAlchemy. I am currently using association table to link two models (following this guide https://flask-sqlalchemy.palletsprojects.com/en/master/models/#many-to-many-relationships). My problem is that this relationship need to have additional attached data. My two models and table are:

log = db.Table('log',
    db.Column('workout_id', db.Integer, db.ForeignKey('workout.id')),
    db.Column('exercise_variant_id', db.Integer, db.ForeignKey('exercise_variant.id')),
    db.Column('quantity', db.Integer, nullable=False),
    db.Column('series', db.Integer, nullable=False)
)

class ExerciseVariant(db.Model):
    __tablename__ = 'exercise_variant'
    id = db.Column(db.Integer, primary_key=True)

class Workout(db.Model):
    __tablename__ = 'workout'
    id = db.Column(db.Integer, primary_key=True)
    exercises = db.relationship('ExerciseVariant', secondary=log, lazy='subquery',
        backref=db.backref('workouts', lazy=True))

This approach is working ok, but the current method for adding records to log table seems a bit hacky to me, since I have to first query both objects to get id I am looking for and then create custom statement:

statement = log.insert().values(
    workout_id=workout.id,
    exercise_variant_id=exercise_variant.id,
    quantity=exercise_dict['quantity'],
    series=exercise_dict['series']
)
db.session.execute(statement)

My questions are:

1. Should this kind of relationship be implemented using Table or Model?

2. If an answer to 1. is Table, can I somehow use backrefs to pass object instances instead of querying and passing their id?

0 Answers
Related