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

zo0M · Answer 1 · 2022-10-24T05:32:12.460

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")

does `Parent` class really `back_populates="parent"` and `Child` class `back_populates="child"`? because in reality `Child` has `__tablename__ = 'right'` and `Parent` has `__tablename__ = 'left'`. there seems to be this typo in sqlalchemy docs, too. — winwin, Mar 12 '23 at 10:03
it is so because both of them refer to the relationship attrs with the corresponding names of the model Association — zo0M, Mar 13 '23 at 15:51

score 0 · Answer 2 · edited Apr 30 '18 at 04:08

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"
    )

I had the same problem, but I used `Association Object` instead of `db.Table`. It is in the same link @mam8cc provided, just one section below: http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#association-object — Rafael, May 01 '18 at 18:32
"I want it to include the food_price column from the restaurants_foods association table". This answer does not answer to the question. — renatodamas, Dec 26 '18 at 12:47

flask sqlalchemy many to many relationship with extra field

2 Answers2

Linked