SQL Alchemy 中的过滤关系 [英] Filtering relationships in SQL Alchemy

问题描述

我有以下场景:

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)

我想做的是加载所有拥有包含 SQL 的书的作者标题.即

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()

看起来很简单.

然后我想做的是迭代作者并显示他们的图书.我希望在访问authors[0].books 时，它只会返回书名中包含SQL"的书籍.但是，我正在分配所有书籍给那个作者.过滤器应用于作者列表，但不应用于他们的当我访问关系时书籍.

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?

推荐答案

请阅读 将显式连接/语句路由到 Eagerly 加载的集合中.然后使用 contains_eager 您可以构建您的查询并获得您想要的内容:

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()

请注意，您实际上是在欺骗 sqlalchemy 以为它已经加载了 Author.books 的所有集合，因此您的会话将知道 false有关世界真实状态的信息.

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.

这篇关于SQL Alchemy 中的过滤关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！