筛选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?

推荐答案

请阅读 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屋！