Filtering relationships in SQL Alchemy

filter, join, python, relationship, sqlalchemy

Solution

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.

Problem

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?

Original source