SQLAlchemy eager loading multiple relationships

Question

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

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!

Answer 1

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

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