Backref broken for detached objects

[Marc Schlaich]

You cannot traverse a backref if the object is detached and the relationship is already joined.

Testcase:

Base = declarative_base()


class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)


class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))
    parent = relationship('Parent', backref='children')


def main():
    engine = create_engine('sqlite:///:memory:')
    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()

    p = Parent()
    p.children.append(Child())
    session.add(p)
    session.commit()

    parents = session.query(Parent).options(joinedload('children')).all()
    session.expunge_all()
    print parents
    print parents[0](0).children
    print parents[0](0).children[0](0).parent

Fails with:

Traceback (most recent call last):
  File "testcase.py", line 44, in <module>
    main()
  File "testcase.py", line 35, in main
    print parents[0](0).children[0](0).parent
  File "/Users/marc/Documents/python/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line 316, in __get__
    return self.impl.get(instance_state(instance), dict_)
  File "/Users/marc/Documents/python/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line 613, in get
    value = self.callable_(state, passive)
  File "/Users/marc/Documents/python/sqlalchemy/lib/sqlalchemy/orm/strategies.py", line 497, in _load_for_state
    (orm_util.state_str(state), self.key)
sqlalchemy.orm.exc.DetachedInstanceError: Parent instance <Child at 0x10654ffd0> is not bound to a Session; lazy load operation of attribute 'parent' cannot proceed

[Marc Schlaich]

Workaround would be:

for p in parents:
    for c in p.children:
        getattr(c, '__dict__')['parent']('parent') = p
print parents[0](0).children[0](0).parent

[Mike Bayer]

This behavior is by design. Implicitly populating the reverse side of a collection would add significant overhead to the object loading process, when it has not been requested (and you can request it, see below). Additionally, reverse-side collection population is not even feasible when the relationship is a many-to-many - if you had a many to many between A<->B, and you were to load a subset of A "ASub", which then loads the list of B objects "BASub", those B objects may refer to many more "A" objects outside of "ASub" and you'd see a lot more rows being loaded than were requested.

In this case, the situation between eager loading the one-to-many also resolving the many-to-one can be achieved most efficiently using the immediateload() directive, since these many-to-ones can all be pulled from the identity map with no additional SQL:

    parents = session.query(Parent).options(joinedload('children'), immediateload("children.parent")).all()
    session.expunge_all()
    print parents
    print parents[0](0).children
    print parents[0](0).children[0](0).parent

Note that SQLAlchemy orients itself towards the "attached" object use case; detachment's primary use case is that of transferring or caching objects to be reattached to another Session for subsequent usage. While working with objects in an explicitly detached state is supported to some degree, it will always have caveats.