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?