orm-8-eager-loading-n-plus-1-problem.py

# N+1 problem refers to the many SELECT statements emitted
# when loading collections against a parent result

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

# Base
Base = declarative_base() 

# Concrete type
class User(Base):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True)
    name = Column(String)

    def __repr__(self):
        return "<User(id: %r, name: %r)>" % (self.id, self.name)

# Engine and create tables
from sqlalchemy import create_engine
engine = create_engine("sqlite://", echo=True)
Base.metadata.create_all(engine)

# Session with identity map
from sqlalchemy.orm import Session
session = Session(bind=engine)

# adding multiple objects as *pending*
u1 = User(name="slavo")
session.add_all([
    u1,
    User(name="jano"),
    User(name="vlado"),
    User(name="peter"),
    User(name="brano")
])

# finalize transaction
session.commit();

# Many-to-One relationship (Adr->User - one user can live on multiple addresses)
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

# in sqlalchemy we have to declare relation ship twice
# 1) relation type at core level
# 2) relationship on orm level and object level
class Address(Base):
    __tablename__ = "address"

    id = Column(Integer, primary_key=True)
    email = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey("user.id"))

    user = relationship("User", backref="addresses") # creates addresses property on referenced object

    def __repr__(self):
        return "<Address(%r)>" % self.email

# Creates addresses table
Base.metadata.create_all(engine)

u1 = User(name="Matus")

u1.addresses = [
    Address(email="matus@matus.com"),
    Address(email="matus@woho.com"),
    Address(email="matus@microsoft.io")
]

session.add(u1) # also added addresses
session.commit()

# -----------------------------------------------------------
# N+1 problem
# -----------------------------------------------------------
for user in session.query(User):
    print(user, user.addresses) # causes N+1 SELECT statements fo(N all users + 1 for initial select of collection)

# -------------------------------------------------------------------
# Eager loading solves N+1 problem by loading all collections at once
# -------------------------------------------------------------------

session.rollback(); # so we can see the load happen again

# more effective for collections
from sqlalchemy.orm import subqueryload
for user in session.query(User).options(subqueryload(User.addresses)):
    print(user, user.addresses)

# ---------------------------------------------------------------------
# joinedload() uses LEFT OUTER JOIN to load parent + child in one query
# ---------------------------------------------------------------------

session.rollback(); # so we can see the load happen again

# more effective for many-to-ones
from sqlalchemy.orm import joinedload
for user in session.query(User).options(joinedload(User.addresses)):
    print(user, user.addresses)


session.rollback(); # so we can see the load happen again

# eager loading does not change the result of the query
# only how related collections are related
for address in session.query(Address).\
    join(Address.user).\
    filter(User.name == "Matus").\
    options(joinedload(Address.user)): # without this it could call two SELECTs, one for addresses and one for users
    print(address, address.user)

# --------------------------------------------------------------------------------------
# to join() and joinload() at the same time without using two JOINs use contains_eager()
# --------------------------------------------------------------------------------------

session.rollback(); # so we can see the load happen again

from sqlalchemy.orm import contains_eager

for address in session.query(Address).\
    join(Address.user).\
    filter(User.name == "Matus").\
    options(contains_eager(Address.user)):
    print(address, address.user)