python - 如何做二级多对多关系？

Question

我在使用 Flask-SQLAlchemy 的 Flask 中工作，并尝试建立多对多对多关系:客户可以有多个订单，订单也可以有多个客户(多对多)；每个订单又包含一个唯一项目列表(一对多)。

我按照SQLAlchemy文档为多对多关系建立了一个关联表，并为一对多关系使用了普通关系/外键；所有引用都设置为 lazy='dynamic'。

association_table = Table('association', Base.metadata,
Column('left_id', Integer, ForeignKey('left.id')),
Column('right_id', Integer, ForeignKey('right.id'))
)

检索与客户端关联的所有项目 的有效方法是什么？我假设 [item for item in order.items.all() for order in client.orders] 会工作(减少上面的问题)，但是有更有效的方法吗？如果结果需要排序怎么办？

---

更新

我现在知道了两种通过以下两种方法(第一种来自 Audrius 的回答)检索客户订单商品的方法:

db.session.query(Item).\
    join(Order).join(Order.clients).\
    filter(Client.id == 42).\
    order_by(Item.timestamp)

和

Item.query.filter(Item.order_id._in(client.order_ids)).order_by(Item.timestamp)‌​

我相信它们都提供了相同的结果，但为了效率应该首选哪一个？

Answer 1

当直接在 SQL 中编写查询时，您将使用连接来有效地检索您想要的数据(如 Rachcha 在他的回答中所展示的那样)。这同样适用于 SQLAlchemy。请参阅 join() 上的 SA 文档更多示例。

如果你的模型定义如下(使用 Flask-SQLAlchemy，因为你用它的标签标记了你的问题):

clients_orders = db.Table('clients_orders',
    db.Column('client_id', db.Integer, db.ForeignKey('client.id'),
              primary_key=True),
    db.Column('order_id', db.Integer, db.ForeignKey('order.id'),
              primary_key=True)
)

class Client(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    orders = db.relationship('Order', secondary=clients_orders,
                             backref='clients')
    # Define other columns...

class Order(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    # Define other columns...

class Item(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    order_id = db.Column(db.Integer, db.ForeignKey('order.id'), nullable=False)
    order = db.relationship('Order', backref='items')
    timestamp = db.Column(db.DateTime)
    # Define other columns...

那么您的查询可能如下所示:

db.session.query(Item).\
    join(Order).join(Order.clients).\
    filter(Client.id == 42).\
    order_by(Item.timestamp)