Filtering relationships in SQL Alchemy

Question

I have the following scenario:

class Author(Base):
  __tablename__ = 'author'

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

  books = relationship('Books', backref='author')


class Book(Base):
  __tablename__ = 'book'

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

What I would like to do is load all authors who have a book containing SQL in the title. i.e.

authors = session.query(Author)\
                 .join(Author.books)\
                 .filter(Book.title.like('%SQL%')\
                 .all()

Seems simple.

What I would then like to do is iterate over the authors and display their books. I would expect that when accessing authors[0].books, it will return ONLY books that have 'SQL' in their title. However, I am getting ALL books assigned to that author. The filter is applied to the list of authors but not their books when I access the relationship.

How can I structure my query such that if I filter on a relationship (i.e. books), when I go to access that relationship, the filtering is still applied?

Answer 1

Please read Routing Explicit Joins/Statements into Eagerly Loaded Collections. Then using contains_eager you can structure your query and get exactly what you want:

authors = (
        session.query(Author)
        .join(Author.books)
        .options(contains_eager(Author.books)) # tell SA that we load "all" books for Authors
        .filter(Book.title.like('%SQL%'))
    ).all()

Please note that you are actually tricking sqlalchemy into thinking that it has loaded all the collection of Author.books, and as such your session will know false information about the real state of the world.