SQLAlchemy - DISTINCT, LIMIT, or OFFSET Causing Subqueries

15 Feb 2017

Updated 2026-07-16: fixed the docs link, confirmed it still applies in 2.0, and added the 2.0 syntax alongside the original.

This part of The Zen of Joined Eager Loading is really important, and still applies in SQLAlchemy 2.0:

When using joined eager loading, if the query contains a modifier that impacts the rows returned externally to the joins, such as when using DISTINCT, LIMIT, OFFSET or equivalent, the completed statement is first wrapped inside a subquery, and the joins used specifically for joined eager loading are applied to the subquery. SQLAlchemy’s joined eager loading goes the extra mile, and then ten miles further, to absolutely ensure that it does not affect the end result of the query, only the way collections and related objects are loaded, no matter what the format of the query is.

I made an example to illustrate this:

from sqlalchemy import create_engine, Column, ForeignKey, Integer from sqlalchemy.orm import relationship, scoped_session, sessionmaker from sqlalchemy.ext.declarative import declarative_base engine = create_engine('mysql://root@localhost/test?charset=utf8mb4', convert_unicode=True) session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine)) Base = declarative_base() Base.query = session.query_property() class Post(Base): __tablename__ = 'posts' id = Column(Integer, primary_key=True) products = relationship( 'Product', lazy='joined', ) class Product(Base): __tablename__ = 'products' id = Column(Integer, primary_key=True) post_id = Column(Integer, ForeignKey('posts.id'), index=True) #Base.metadata.drop_all(engine) Base.metadata.create_all(engine) first_result = Post.query.first() if not first_result: for x in range(50): products = [Product() for y in range(12)] session.add(Post(products=products)) session.commit() print(Post.query.limit(20))

On MySQL this can be responsible for some really poor query performance, because it can cause it to use temporary tables and filesort.

The best way I’ve found to prevent the subqueries is by first querying for the ids only, then running another query that includes all relations:

ids = session.query(Product.id).limit(20)
Product.query.filter(Product.id.in_(ids))

In SQLAlchemy 2.0, the same approach with select():

ids = select(Product.id).limit(20)
products = session.scalars(
    select(Product).where(Product.id.in_(ids))
).all()