python - SQLAlchemy Joinedload过滤列

Question

您好，我想使用 joinedload 对我的查询进行过滤。但我似乎无法让它发挥作用。下面是我的示例查询

result = (
        session.query(Work).
        options(
            joinedload(Work.company_users).
            joinedload(CompanyUser.user)
        ).
        filter(Work.id == 1).
        filter(User.first_name == 'The name').  <<--- I can't get this to work.
        all()
    )

运行此命令时，它返回的行超出了我的预期。真正的结果应该只返回 8 行。但是在执行此查询时，它返回 234 行，这比我预期的要多得多

Answer 1

它不起作用的原因是 joinedload(以及所有其他关系加载技术)应该是完全透明的。也就是说，在您的查询中有一个 joinedload 不应该以任何其他方式影响它，而不是导致关系被填充。你应该阅读 "The Zen of Joined Eager Loading" , 开头为:

Since joined eager loading seems to have many resemblances to the use of Query.join(), it often produces confusion as to when and how it should be used. It is critical to understand the distinction that while Query.join() is used to alter the results of a query, joinedload() goes through great lengths to not alter the results of the query, and instead hide the effects of the rendered join to only allow for related objects to be present.

其中一个技巧是为不可用的连接表使用别名。然后，您的查询最终会在 Work 和 User 之间执行隐式交叉连接，因此会产生额外的行。因此，为了过滤连接表，请使用 Query.join() :

session.query(Work).\
    join(Work.company_users).\
    join(CompanyUser.user).\
    filter(Work.id == 1).\
    filter(User.first_name == 'The name').\
    all()

如果您还需要适当的预加载，您可以指示查询它已经包含与 contains_eager() 的连接。 :

session.query(Work).\
    join(Work.company_users).\
    join(CompanyUser.user).\
    options(contains_eager(Work.company_users).
            contains_eager(CompanyUser.user)).\
    filter(Work.id == 1).\
    filter(User.first_name == 'The name').\
    all()

注意对 contains_eager() 的链式调用。