如何实现两级多对多关系？

Question

我正在使用Flask和Flask-SQLAlchemy，想要建立一个多对多对多的关系：客户可以有多个订单，而订单也可以有多个客户（多对多）；每个订单又包含一个独特的商品列表（单对多）。

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

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

有什么有效的方法可以获取与某个客户相关的所有items吗？我假设[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查询的时候，你会使用连接（joins）来有效地获取你想要的数据（就像Rachcha在他的回答中展示的那样）。这同样适用于SQLAlchemy。你可以参考SQLAlchemy的文档，了解更多关于join()的例子。

如果你的模型定义如下（使用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)