SQLAlchemy渴望加载多个关系 [英] SQLAlchemy eager loading multiple relationships

问题描述

SQLAlchemy支持渴望加载关系，它基本上是一个JOIN语句.但是，如果模型具有两个或多个关系，则可能是非常庞大的联接.例如，

SQLAlchemy supports eager load for relationship, it is basically a JOIN statement. However, if a model has two or more relationships, it could be a very huge join. For example,

class Product(Base):
    __tablename__ = 'product'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(255), nullable=False)
    orders = relationship('Order', backref='product', cascade='all')
    tags = relationship('Tag', secondary=product_tag_map)

class Order(Base):
    __tablename__ = 'order'
    id = Column(Integer, primary_key=True, autoincrement=True)
    date = Column(TIMESTAMP, default=datetime.now())

class Tag(Base):
    __tablename__ = 'tag'
    id = Column(Integer, primary_key=True, autoincrement=True)
    tag_type = Column(String(255), nullable=False)
    tag_value = Column(String(255), nullable=False)

q = session.query(Product).join(User.addresses)\
    .options(joinedload(Product.orders))\
    .options(joinedload(Product.tags)).all()

此查询的性能确实很差，因为Order和Tag的JOIN会生成一个巨大的表.但是Order和Tag在这里没有关系，因此它们不应为JOIN.它应该是两个分开的查询.而且由于会话具有某种程度的缓存，所以我将查询更改为此.

The performance of this query is really bad, because the JOIN of Order and Tag will generate a huge table. But the Order and Tag has no relationship in here, so they should not be JOIN. It should be two separated queries. And because the session has some level of caching, so I changed my query to this.

session.query(Product).join(Product.order) \
    .options(joinedload(Product.tags)).all()

q = session.query(Product).join(User.addresses) \
    .options(joinedload(Product.cases)).all()

这次性能要好得多.但是，我不认为这样做是正确的.我不确定会话结束时标签缓存是否会过期.

This time the performance is way much better. However, I am not convinced that this is the correct to do it. I am not sure if the caches of tags will be expired when the session ends.

请让我知道此类查询的适当方式.谢谢！

Please let me know the appropriate way for this kind of query. Thank you!

推荐答案

对于一对多或多对多关系，(通常)最好使用

For a one-to-many or many-to-many relationship, it's (usually) better to use subqueryload instead, for performance reasons:

session.query(Product).join(User.addresses)\
    .options(subqueryload(Product.orders),\
             subqueryload(Product.tags)).all()

此问题分别对orders和tags发出SELECT查询.

This issues separate SELECT queries for each of orders and tags.

这篇关于SQLAlchemy渴望加载多个关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！