SQLAlchemy eager loading multiple relationships : <p>SQLAlchemy supports eager load for relationship, it is basically a <code>JOIN</code> statement. However, if a model has two or more relationships, it could be a very huge join. For example,</p>

<pre><code>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()
</code></pre>

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

<pre><code>session.query(Product).join(Product.order) \
    .options(joinedload(Product.tags)).all()

q = session.query(Product).join(User.addresses) \
    .options(joinedload(Product.cases)).all()
</code></pre>

<p>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.</p>

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