问题

我有两个表，`Products` 和 `Orders`，在我的 Flask-SqlAlchemy 设置中，它们相互关联，一个订单可以有多个产品：

```python
class Products(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    ....

class Orders(db.Model):
    guid = db.Column(db.String(36), default=generate_uuid, primary_key=True)
    products = db.relationship(
        "Products", secondary=order_products_table, backref="orders")
    ....

通过以下方式关联：

order_products_table = db.Table("order_products_table",
    db.Column('orders_guid', db.String(36), db.ForeignKey('orders.guid')),
    db.Column('products_id', db.Integer, db.ForeignKey('products.id'))
    # db.Column('license', dbString(36))
)

对于我的需求，订单中的每个产品都将获得一个唯一的许可证字符串，逻辑上应该将其添加到每个订单中的产品的 order_products_table 行中。

如何声明在连接表 order_products_table 上的第三个 license 列，以便在插入订单时填充它？

<details>
<summary>英文:</summary>

I have two tables, `Products` and `Orders`, inside my Flask-SqlAlchemy setup, and they are linked so an order can have several products:

class Products(db.Model):
id = db.Column(db.Integer, primary_key=True)
....

class Orders(db.Model):
guid = db.Column(db.String(36), default=generate_uuid, primary_key=True)
products = db.relationship(
"Products", secondary=order_products_table, backref="orders")
....

linked via:

order_products_table = db.Table("order_products_table",
db.Column('orders_guid', db.String(36), db.ForeignKey('orders.guid')),
db.Column('products_id', db.Integer, db.ForeignKey('products.id'))
# db.Column('license', dbString(36))
)

For my purposes, each product in an order will receive a unique license string, which logically should be added to the `order_products_table` rows of each product in an order.

How do I declare this third `license` column on the join table `order_products_table` so it gets populated it as I insert an Order?

</details>


# 答案1
**得分**: 2

我后来找到了SQLAlchemy文档中关于[Association Object](https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#association-object)的文档，它允许对连接表进行扩展。 

**更新后的设置:**

```python
# 不再使用表，提供一个具有额外字段和显式键和back_populates的JOIN表模型：
class OrderProducts(db.Model):
    __tablename__ = 'order_products_table'
    orders_guid = db.Column(db.String(36), db.ForeignKey(
        'orders.guid'), primary_key=True)
    products_id = db.Column(db.Integer, db.ForeignKey(
        'products.id'), primary_key=True)

    order = db.relationship("Orders", back_populates="products")
    products = db.relationship("Products", back_populates="order")

    licenses = db.Column(db.String(36), nullable=False)

class Products(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    order = db.relationship(OrderProducts, back_populates="order")
    ....

class Orders(db.Model):
    guid = db.Column(db.String(36), default=generate_uuid, primary_key=True)
    products = db.relationship(OrderProducts, back_populates="products")
    ....

真正棘手的部分（但也在文档页面上显示）是如何插入数据。在我的情况下，操作如下：

o = Orders(...) # 插入其他数据
for id in products:
    # 创建带有额外数据（如licenses）的OrderProducts连接行
    join = OrderProducts(licenses="Foo")
    # 向JOIN添加产品
    join.products = Products.query.get(id)
    # 将填充的JOIN添加为订单产品
    o.products.append(join)

# 最后提交到数据库
db.session.add(o)
db.session.commit()

我最初试图直接填充Order.products（或示例代码中的o.products），但这将导致错误，因为它期望一个OrderProducts类而不是Products类。

我还遇到了字段命名和引用back_populates的问题。再次注意，上面的示例以及文档中都展示了这一点。请注意，复数形式的命名完全取决于您希望字段如何命名。

# Instead of a table, provide a model for the JOIN table with additional fields 
# and explicit keys and back_populates:
class OrderProducts(db.Model):
    __tablename__ = 'order_products_table'
    orders_guid = db.Column(db.String(36), db.ForeignKey(
        'orders.guid'), primary_key=True)
    products_id = db.Column(db.Integer, db.ForeignKey(
        'products.id'), primary_key=True)

    order = db.relationship("Orders", back_populates="products")
    products = db.relationship("Products", back_populates="order")

    licenses = db.Column(db.String(36), nullable=False)

class Products(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    order = db.relationship(OrderProducts, back_populates="order")
    ....

class Orders(db.Model):
    guid = db.Column(db.String(36), default=generate_uuid, primary_key=True)
    products = db.relationship(OrderProducts, back_populates="products")
    ....

o = Orders(...) # insert other data
for id in products:
    # Create OrderProducts join rows with the extra data, e.g. licenses
    join = OrderProducts(licenses="Foo")
    # To the JOIN add the products 
    join.products = Products.query.get(id)
    # Add the populated JOIN as the Order products
    o.products.append(join)

# Finally commit to database
db.session.add(o)
db.session.commit()