example of subquery loading using temporary tables and filesort

subquery_nested_loading.py

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,
                       echo=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='subquery', backref='post')


class Product(Base):
    __tablename__ = 'products'
    id = Column(Integer, primary_key=True)
    post_id = Column(Integer, ForeignKey('posts.id'), index=True)

    links = relationship('ProductLink', lazy='subquery')


class ProductLink(Base):
    __tablename__ = 'links'
    id = Column(Integer, primary_key=True)
    product_id = Column(Integer, ForeignKey('products.id'), index=True)


Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

# create new rows if database is empty
first_result = Post.query.first()
if not first_result:
    for x in range(50):
        products = [Product(links=[ProductLink() for link in range(8)]) for y in range(12)]
        session.add(Post(products=products))
    session.commit()

# example of subquery loading using temporary tables and filesort
posts = Post.query.limit(20).all()
for post in posts:
    for product in post.products:
        print(product.id)

"""
explain SELECT links.id AS links_id, links.product_id AS links_product_id, products_1.id AS products_1_id
FROM (SELECT posts.id AS posts_id
FROM posts
 LIMIT 20) AS anon_1 INNER JOIN products AS products_1 ON anon_1.posts_id = products_1.post_id INNER JOIN links ON products_1.id = links.product_id ORDER BY products_1.id

id  select_type table type  possible_keys key key_len ref rows  Extra
1 PRIMARY <derived2>  ALL NULL  NULL  NULL  NULL  20  Using temporary; Using filesort
1 PRIMARY products_1  ref PRIMARY,ix_products_post_id ix_products_post_id 5 anon_1.posts_id 1 Using index
1 PRIMARY links ref ix_links_product_id ix_links_product_id 5 test.products_1.id  1 Using index
2 DERIVED posts index NULL  PRIMARY 4 NULL  50  Using index
"""