django 的 .extra(where= 子句被表重命名 .filter(foo__in=... subselects 破坏)

发布于 2024-09-10 20:18:25 字数 2516 浏览 4 评论 0原文

简而言之,过滤器内所有查询的表名都被重命名为 u0、u1、...,因此我的额外 where 子句将不知道要指向哪个表。我希望不必为我可能对这些数据进行子选择的每种方式手动进行所有查询,我当前的解决方法是将我的额外查询转换为 pk value_lists,但这些确实很慢并且令人厌恶。

这就是这一切的样子。除了指向 products_product.id 的第一条 sql 行之外,您基本上可以忽略此管理器方法的 extra 内容的详细信息:

def by_status(self, *statii):
    return self.extra(where=["""products_product.id IN                                                                                                                                                  
        (SELECT recent.product_id                                                                                                                                                                          
          FROM (                                                                                                                                                                                           
            SELECT product_id, MAX(start_date) AS latest                                                                                                                                                   
            FROM products_productstatus                                                                                                                                                                    
            GROUP BY product_id                                                                                                                                                                            
          ) AS recent                                                                                                                                                                                      
          JOIN products_productstatus AS ps ON ps.product_id = recent.product_id                                                                                                                           
          WHERE ps.start_date = recent.latest                                                                                                                                                              
            AND ps.status IN (%s))""" % (', '.join([str(stat) for stat in statii]),)])

这对于仅涉及 products_product 表的所有情况都非常有效。

当我希望这些产品作为子选择时,我会这样做:

Piece.objects.filter(
    product__in=Product.objects.filter(
        pk__in=list(
            Product.objects.by_status(FEATURED).values_list('id', flat=True))))

如何保留查询集的通用能力,同时仍然使用额外的 where 子句?

The short of it is, the table names of all queries that are inside a filter get renamed to u0, u1, ..., so my extra where clauses won't know what table to point to. I would love to not have to hand-make all the queries for every way I might subselect on this data, and my current workaround is to turn my extra'd queries into pk values_lists, but those are really slow and something of an abomination.

Here's what this all looks like. You can mostly ignore the details of what goes in the extra of this manager method, except the first sql line which points to products_product.id:

def by_status(self, *statii):
    return self.extra(where=["""products_product.id IN                                                                                                                                                  
        (SELECT recent.product_id                                                                                                                                                                          
          FROM (                                                                                                                                                                                           
            SELECT product_id, MAX(start_date) AS latest                                                                                                                                                   
            FROM products_productstatus                                                                                                                                                                    
            GROUP BY product_id                                                                                                                                                                            
          ) AS recent                                                                                                                                                                                      
          JOIN products_productstatus AS ps ON ps.product_id = recent.product_id                                                                                                                           
          WHERE ps.start_date = recent.latest                                                                                                                                                              
            AND ps.status IN (%s))""" % (', '.join([str(stat) for stat in statii]),)])

Which works wonderfully for all the situations involving only the products_product table.

When I want these products as a subselect, i do:

Piece.objects.filter(
    product__in=Product.objects.filter(
        pk__in=list(
            Product.objects.by_status(FEATURED).values_list('id', flat=True))))

How can I keep the generalized abilities of a query set, yet still use an extra where clause?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

撧情箌佬 2024-09-17 20:18:25

首先:这个问题对我来说并不完全清楚。您问题中的第二个代码块是您要执行的实际代码吗?如果是这种情况,查询应该按预期工作,因为没有执行子选择。

我假设您想使用第二个代码块,而子选择周围没有 list() ,以防止执行第二个查询。


django文档提到了这个问题 在有关额外方法的文档中。然而,克服这个问题并不容易。

最简单但最“哈克式”的解决方案是观察 django 在 extra 方法中为要查询的表生成了哪个表别名。只要您始终以相同的方式构建查询(不更改多个额外方法或filter的顺序,您就可以依赖此别名的持久命名)导致加入的调用)。

您可以使用以下命令检查将在数据库查询集中执行的查询:

print Model.objects.filter(...).query

这将显示用于您要查询的表的别名。

At first: the issue is not totally clear to me. Is the second code block in your question the actual code you want to execute? If this is the case the query should work as expected since there is no subselect performed.

I assume so that you want to use the second code block without the list() around the subselect to prevent a second query being performed.


The django documentation refers to this issue in the documentation about the extra method. However its not very easy to overcome this issue.

The easiest but most "hakish" solution is to observe which table alias is produced by django for the table you want to query in the extra method. You can rely on the persistent naming of this alias as long as you construct the query always in the same fashion (you don't change the order of multiple extra methods or filter calls that cause a join).

You can inspect a query that will be execute in the DB queryset by using:

print Model.objects.filter(...).query

This will reveal the aliases that are used for the tables you want to query.

时光磨忆 2024-09-17 20:18:25

从 Django 1.11 开始,您应该能够使用 SubqueryOuterRef 生成与 extra 等效的查询(使用相关子查询而不是a join):

def by_status(self, *statii):
    return self.filter(
        id__in=Subquery(ProductStatus.values("product_id").filter(
            status__in=statii,
            product__in=Subquery(ProductStatus.objects.values(
                "product_id",
            ).annotate(
                latest=Max("start_date"),
            ).filter(
                latest=OuterRef("start_date"),
            ).values("product_id"),
        ),
    )

您也可以使用 Window 表达式来实现(从 Django 2.0 开始)。

请注意,这尚未经过测试,因此可能需要一些调整。

As of Django 1.11, you should be able to use Subquery and OuterRef to generate an equivalent query to your extra (using a correlated subquery rather than a join):

def by_status(self, *statii):
    return self.filter(
        id__in=Subquery(ProductStatus.values("product_id").filter(
            status__in=statii,
            product__in=Subquery(ProductStatus.objects.values(
                "product_id",
            ).annotate(
                latest=Max("start_date"),
            ).filter(
                latest=OuterRef("start_date"),
            ).values("product_id"),
        ),
    )

You could probably do it with Window expressions as well (as of Django 2.0).

Note that this is untested, so may need some tweaks.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文