django 的 .extra(where= 子句被表重命名 .filter(foo__in=... subselects [英] django's .extra(where= clauses are clobbered by table-renaming .filter(foo__in=... subselects

问题描述

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

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.

这就是这一切的样子.除了指向 products_product.id 的第一条 sql 行之外，您几乎可以忽略此管理器方法的额外内容的详细信息:

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]),)])

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

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

我怎样才能保持查询集的泛化能力，但仍然使用额外的 where 子句?

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

推荐答案

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

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.

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

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.

django 文档参考了这个问题 在关于额外方法的文档中.然而，要克服这个问题并不容易.

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

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

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

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

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.

这篇关于django 的 .extra(where= 子句被表重命名 .filter(foo__in=... subselects的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！