flask sqlalchemy many to many relationship with extra field

Question

I have 2 tables: restaurants and foods, and a 3rd table restaurants_foods which stores the many to many relationship between the 2 tables

restaurants_foods = db.Table('restaurants_foods',
    db.Column('restaurant_id', db.Integer, db.ForeignKey('restaurants.id'), primary_key=True),
    db.Column('food_id', db.Integer, db.ForeignKey('foods.id'), primary_key=True),
    db.Column('food_price', db.Float)
)

class Food(Model):
    __tablename__ = "foods"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)    
    name = db.Column(db.String(255), nullable=False)
    description = db.Column(db.String(255), nullable=True)


class Restaurant(Model):
    __tablename__ = "restaurants"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)    
    name = db.Column(db.String(255), nullable=False)
    foods = db.relationship('Food', secondary=restaurants_foods)

Now when i query Restautant.query.get(1).foods, I want it to include the food_price column from the restaurants_foods association table

Answer 1

You have to use an association object pattern (is a variant of definition on many-to-many): it’s used when your association table contains additional columns beyond those which are foreign keys to the left and right tables. Instead of using the relationship.secondary argument, you map a new class directly to the association table. The left side of the relationship references the association object via one-to-many, and the association class references the right side via many-to-one. Below illustrate an association table mapped to the Association class which includes a column called extra_data, which is a string value that is stored along with each association between Parent and Child:

class Association(Base):
    __tablename__ = 'association'
    left_id = Column(Integer, ForeignKey('left.id'), primary_key=True)
    right_id = Column(Integer, ForeignKey('right.id'), primary_key=True)
    extra_data = Column(String(50))
    child = relationship("Child", back_populates="parents")
    parent = relationship("Parent", back_populates="children")

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Association", back_populates="parent")

class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)
    parents = relationship("Association", back_populates="child")

Answer 2

Please take a look at configuring many-to-many relationships in SQLAlchemy. You're going to want something like this:

restaurants_foods = db.Table('restaurants_foods',
    db.Column('restaurant_id', db.Integer, db.ForeignKey('restaurants.id'), primary_key=True),
    db.Column('food_id', db.Integer, db.ForeignKey('foods.id'), primary_key=True),
    db.Column('food_price', db.Float))


class Food(Model):
    __tablename__ = "foods"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(255), nullable=False)
    description = db.Column(db.String(255), nullable=True)
    restaurants = relationship(
        "Restaurant",
        secondary=restaurant_foods,
        back_populates="foods"
    )


class Restaurant(Model):
    __tablename__ = "restaurants"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(255), nullable=False)
    foods = relationship(
        "Food",
        secondary=restaurant_foods,
        back_populates="restaurants"
    )