Improve docs for `contains_eager`

[Ian McCullough]

It took me a really long time to discover, and then figure out how to use contains_eager for the use case of:

Load entity A (which has a to-many relationship to B) populating its list of Bs with the subset of related Bs that meet a certain filter criteria.

The existing docs for contains_eager don't express well its applicability for this goal, and neither of the examples show any filtering. The closest they come is:

The above query would join from the Order entity to its related User entity, and the returned Order objects would have the Order.user attribute pre-populated.

It took me a long time to figure out how this was different from joinedload and to understand that it could filter relationship collections. Even just a trivial example that showed a filter on the related entity (and how the resulting graph would look different than it would without contains_eager) would go a long way. Thanks!

[Mike Bayer]

can i confirm that you have seen and read http://docs.sqlalchemy.org/en/rel_1_0/orm/loading_relationships.html?highlight=contains_eager#contains-eager ? it is right at the bottom of the contains_eager docstring you refer to as a "seealso".

[Ian McCullough]

Yes, I've seen and read that. I just went back and re-read both sections again, very slowly and deliberately, and knowing what I know now, I can see how maybe, if I really, really stretch, I might have been able to infer the behavior of the "subset of Bs" case, but it's definitely not clear. I really think it would be far more explicit and readily grasp-able with an example that explicitly includes filtering. If it helps, I cooked up this example that, had I seen it, would have instantly made clear the "subset of Bs" case:

from sqlalchemy import *
from sqlalchemy import event
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker, contains_eager, joinedload

engine = create_engine('sqlite://')
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()


class User(Base):
    __tablename__ = "user"
    id = Column('user_id', Integer, primary_key=True, autoincrement=True)
    name = Column('user_name', String(64), nullable=False)
    nicknames = relationship('Nickname', backref="user", cascade="all, delete-orphan")


class Nickname(Base):
    __tablename__ = "nicknames"
    id = Column('nick_id', Integer, primary_key=True, autoincrement=True)
    user_id = Column('user_id', Integer, ForeignKey("user.user_id"), nullable=False)
    nickname = Column('nickname', String(64), nullable=False)

Base.metadata.create_all(engine)

# Populate the tables...
mrt = User(name="Lawrence Tureaud", nicknames=[Nickname(nickname="Mr. T"),
                                               Nickname(nickname="B.A. Baracus"),
                                               Nickname(nickname="Clubber Lang")])
session.add(mrt)
session.commit()

# Track the number of queries for each approach...
num_queries = 0
def increment(*_):
    global num_queries
    num_queries += 1

event.listen(engine, "before_cursor_execute", increment)

# Demonstrate no hints...
misters = session.query(User). \
    join(User.nicknames). \
    filter(Nickname.nickname.startswith("Mr.")).all()

print "With no loading hints, the user record has %d nicknames (%s), and executed %d queries to load." % \
      (len(misters[0].nicknames), ', '.join(x.nickname for x in misters[0].nicknames), num_queries)
# Prints: With no loading hints, the user record has 3 nicknames (Mr. T, B.A. Baracus, Clubber Lang),
#         and executed 2 queries to load.
session.rollback()
num_queries = 0

# Demonstrate joinedload...
misters = session.query(User). \
    join(User.nicknames). \
    options(joinedload(User.nicknames)). \
    filter(Nickname.nickname.startswith("Mr.")).all()

print "With joinedload, the user record has %d nicknames (%s), and executed %d queries to load." % \
      (len(misters[0].nicknames), ', '.join(x.nickname for x in misters[0].nicknames), num_queries)
# Prints: With joinedload, the user record has 3 nicknames (B.A. Baracus, Clubber Lang, Mr. T),
#         and executed 1 queries to load.
session.rollback()
num_queries = 0

# Demonstrate contains_eager...
misters = session.query(User). \
    join(User.nicknames). \
    options(contains_eager(User.nicknames)). \
    filter(Nickname.nickname.startswith("Mr.")).all()

print "With contains_eager, the user record has %d nicknames (%s), and executed %d queries to load." % \
      (len(misters[0].nicknames), ', '.join(x.nickname for x in misters[0].nicknames), num_queries)
# Prints: With contains_eager, the user record has 1 nicknames (Mr. T),
#         and executed 1 queries to load.
session.rollback()
num_queries = 0

[Mike Bayer]

• add documentation that describes, then proceeds to warn about the many caveats and confusing effects of, the popular approach of using contains_eager() to alter the natural result of a related collection. I'm not a fan of this technique as it changes the semantics of a relationship in such a way that the rest of the ORM isn't aware of and it also can be undone very easily; hence the section needs as much text for warnings as for describing the technique itself. fixes #3563

→ <<cset 1dc805dd4d90>>

[Mike Bayer]

• add documentation that describes, then proceeds to warn about the many caveats and confusing effects of, the popular approach of using contains_eager() to alter the natural result of a related collection. I'm not a fan of this technique as it changes the semantics of a relationship in such a way that the rest of the ORM isn't aware of and it also can be undone very easily; hence the section needs as much text for warnings as for describing the technique itself. fixes #3563

(cherry picked from commit 1dc805dd4d902b9204703f0bd6151c58f1f287af)

→ <<cset 423697dea60c>>