[Sqlalchemy-tickets] Issue #4021: Related objects are not reloaded when using contains_eager (zzzeek/sqlalchemy)

[Boris S. <iss...@bi...>]

New issue 4021: Related objects are not reloaded when using contains_eager
https://bitbucket.org/zzzeek/sqlalchemy/issues/4021/related-objects-are-not-reloaded-when

Boris Serebrov:

I have a query like this (where `Author` can have many `Book`s):

```
#!python

query = session.query(Author)
query = query.join(Author.books)
query = query.filter(Book._id == book1._id)
query = query.options(contains_eager(Author.books))
author = query.one()
```

It works as expected, I get the `author` object with one related book (`book1`).

Now, if I run same query, but filter it by `book2._id`, I get the `author` object which still has `book1` in the `author.books`.

Tracing the issue through the SQLAlchemy code, I found a workaround - I can use `populate_existing()` and then it works. But it doesn't look like a good solution, I feel like it should work without it (or that there is a better workaround - is there a way to instruct SQLAlchemy that specific relation need to be reloaded?).

The complete example:

```
#!python

from sqlalchemy import __version__
from sqlalchemy import create_engine, ForeignKey, Column, Integer, String
from sqlalchemy.orm import relationship, backref, contains_eager, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

ModelBase = declarative_base()


class Author(ModelBase):
    __tablename__ = 'authors'
    _id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String(255))


class Book(ModelBase):
    __tablename__ = 'books'
    _id = Column(Integer, primary_key=True)
    name = Column(String)
    author_id = Column(Integer, ForeignKey('authors._id'))
    author = relationship(
        'Author', backref=backref('books'))


if __name__ == "__main__":
    print('SQLAlchemy version', __version__)
    engine = create_engine("sqlite://")
    ModelBase.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()

    author = Author(name="author1")
    session.add(author)
    session.commit()

    book1 = Book(name="book_a1_1", author_id=author._id)
    session.add(book1)
    book2 = Book(name="book_a1_2", author_id=author._id)
    session.add(book2)
    session.commit()
    print('book1', book1._id)
    print('book2', book2._id)
    print()

    query = session.query(Author)
    query = query.join(Author.books)
    query = query.filter(Book._id == book1._id)
    query = query.options(contains_eager(Author.books))
    print('Expect [book1]', [x._id for x in query.one().books])
    assert query.one().books[0]._id == book1._id

    query = session.query(Author)
    # it works with populate_existing()
    # query = session.query(Author).populate_existing()
    query = query.join(Author.books)
    query = query.filter(Book._id == book2._id)
    query = query.options(contains_eager(Author.books))
    print('Expect [book2]', [x._id for x in query.one().books])
    assert query.one().books[0]._id == book2._id

```

And the output:

```
SQLAlchemy version 1.1.11
book1 1
book2 2

Expect [book1] [1]
Expect [book2] [1]
Traceback (most recent call last):
  File "contains_eager.py", line 58, in <module>
    assert query.one().books[0]._id == book2._id
AssertionError

```

The second assert fails - there is still `book1` in the related `books` property.
I reproduced this initially with version `1.0.11`, tested on `1.1.11` - it works the same.