flask sqlalchemy与额外字段的多对多关系 [英] flask sqlalchemy many to many relationship with extra field

问题描述

我有2个表:餐厅和食物，还有第3个表Restaurants_foods，用于存储2个表之间的多对多关系

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)

现在，当我查询Restautant.query.get(1).foods时，我希望它包含Restaurants_foods关联表中的food_price列

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

推荐答案

您必须使用关联对象模式(是多对多定义的变体):当关联表包含除左和右表外键以外的其他列时，将使用该模式.您可以将新类直接映射到关联表，而不是使用Relationship.secondary参数.关系的左侧通过一对多引用关联对象，关联类通过多对一引用右侧.下面说明了映射到Association类的关联表，该表包括一列称为extra_data的列，该列是与父级和子级之间的每个关联一起存储的字符串值:

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

这篇关于flask sqlalchemy与额外字段的多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！