SQLAlchemy Joinedload 过滤列 [英] SQLAlchemy Joinedload filter column

问题描述

我想使用joinedload 对我的查询进行过滤.但我似乎无法让它发挥作用.以下是我的示例查询

Hi I would like to do a filter with my query using a joinedload. But I can't seem to make it work. Below is my sample query

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 行，这比我预期的要多得多

When running this it's returning a row that is more than what I'm expecting. The real result should only return 8rows. But upon executing this query it returns 234 rows which is way more than what I expect

推荐答案

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

The reason it is not working is that joinedload (and all the other relationship loading techniques) are meant to be entirely transparent. That is to say having a joinedload in your query should not affect it in any other way other than resulting in the relationships being filled. You should read "The Zen of Joined Eager Loading", which begins with:

由于加入的急切加载似乎与 Query.join() 的使用有很多相似之处，因此它经常会混淆何时以及如何使用它.虽然 Query.join() 用于更改查询的结果，但 joinedload() 会竭尽全力不更改结果，了解这一区别至关重要查询，而是隐藏呈现的连接的效果，只允许相关对象出现.

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

One of the tricks is to use aliases for the joined tables which are not made available. Your query then ends up performing an implicit cross-join between Work and User, and hence the extra rows. So in order to filter against a joined table, use 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():

and if you also need the eagerloads in place, you can instruct the Query that it already contains the joins with 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() 的链式调用.

Note the chained calls to contains_eager().

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