Filtering relationships in SQL Alchemy

问题:

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?

回答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.

回答2:

In short, this is not possible. (If it were, an Author instance would have different books attribute depending on how it was queried, which doesn't make sense.)

What you could do instead is query the reverse relationship:

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

Then you can access .author on each book to collect books written by the same author together.