如何使用子查询作为 WHERE 子句的一部分编写 Django 查询? [英] How do I write a Django query with a subquery as part of the WHERE clause?

问题描述

我使用的是 Django 和 Python 3.7.我在弄清楚如何编写 Django 查询时遇到了麻烦，其中子查询作为 where 子句的一部分.这是模型......

I'm using Django and Python 3.7. I'm having trouble figuring out how to write a Django query where there's a subquery as part of a where clause. Here's the models ...

class Article(models.Model):
    objects = ArticleManager()
    title = models.TextField(default='', null=False)
    created_on = models.DateTimeField(auto_now_add=True)


class ArticleStat(models.Model):
    objects = ArticleStatManager()
    article = models.ForeignKey(Article, on_delete=models.CASCADE, related_name='articlestats')
    elapsed_time_in_seconds = models.IntegerField(default=0, null=False)
    votes = models.FloatField(default=0, null=False)


class StatByHour(models.Model):
    index = models.FloatField(default=0)
    # this tracks the hour when the article came out
    hour_of_day = IntegerField(
        null=False,
        validators=[
            MaxValueValidator(23),
            MinValueValidator(0)
        ]
    )

在 PostGres 中，查询类似于

In PostGres, the query would look similar to

SELECT *
FROM article a,
     articlestat ast
WHERE a.id = ast.article_id
  AND ast.votes > 100 * (
    SELECT "index" 
    FROM statbyhour 
    WHERE hour_of_day = extract(hour from (a.created_on + 1000 * interval '1 second')))

注意子查询是 WHERE 子句的一部分

Notice the subquery as part of the WHERE clause

ast.votes > 100 * (select index from statbyhour where hour_of_day = extract(hour from (a.created_on + 1000 * interval '1 second'))) 

所以我想我可以做这样的事情......

So I thought I could do something like this ...

hour_filter = Func(
    Func(
        (F("article__created_on") + avg_fp_time_in_seconds * "interval '1 second'"),
        function='HOUR FROM'),
    function='EXTRACT')
...
votes_criterion2 = Q(votes__gte=F("article__website__stats__total_score") / F(
    "article__website__stats__num_articles") * settings.TRENDING_PCT_FLOOR *
                                StatByHour.objects.get(hour_of_day=hour_filter) * day_of_week_index)
qset = ArticleStat.objects.filter(votes_criterion1 & votes_criterion2,
                                  comments__lte=25)

但这会导致无法将关键字‘文章’解析为字段.选项为:hour_of_day、id、index、num_articles、total_score"错误.我认为这是因为 Django 正在运行其中的较大查询之前评估我的StatByHour.objects"查询，但我不知道如何重写以使子查询同时运行.

but this results in a "Cannot resolve keyword 'article' into field. Choices are: hour_of_day, id, index, num_articles, total_score" error. I think this is because Django is evaulating my "StatByHour.objects" query before the larger query within it is run, but I don't know how to rewrite things to get the subquery to run at the same time.

K，将我的子查询移动到实际的子查询"函数中并引用我使用 OuterRef 创建的过滤器 ...

K, moved my subquery into an actual "Subquery" function and referenced the filter I created using OuterRef ...

hour_filter = Func(
    Func(
        (F("article__created_on") + avg_fp_time_in_seconds * "interval '1 second'"),
        function='HOUR FROM'),
    function='EXTRACT')
query = StatByHour.objects.get(hour_of_day=OuterRef(hour_filter))


...
votes_criterion2 = Q(votes__gte=F("article__website__stats__total_score") / F(
    "article__website__stats__num_articles") * settings.TRENDING_PCT_FLOOR *
                                Subquery(query) * 
                 day_of_week_index)
qset = ArticleStat.objects.filter(votes_criterion1 & votes_criterion2,
                                  comments__lte=25)

这会导致

This queryset contains a reference to an outer query and may only be used in a subquery.

这很奇怪，因为我在子查询中使用它.

which is odd because I am using it in a subquery.

编辑 #2: 即使根据给出的答案更改查询...

Edit #2: Even after changing the query per the answer given ...

hour_filter = Func(
    Func(
        (F("article__created_on") + avg_fp_time_in_seconds * "interval '1 second'"),
        function='HOUR FROM'),
    function='EXTRACT')
query = StatByHour.objects.filter(hour_of_day=OuterRef(hour_filter))[:1]

...
votes_criterion2 = Q(votes__gte=F("article__website__stats__total_score") / F(
    "article__website__stats__num_articles") * settings.TRENDING_PCT_FLOOR *
                                Subquery(query) *
                                day_of_week_index)
qset = ArticleStat.objects.filter(et_criterion1 & et_criterion2 & et_criterion3,
                                  votes_criterion1 & votes_criterion2,
                                  article__front_page_first_appeared_date__isnull=True,
                                  comments__lte=25)

我仍然收到错误

'Func' object has no attribute 'split'

推荐答案

子查询 需要是不会立即求值的查询，以便它们的求值可以推迟到运行外部查询.get() 不符合要求，因为它立即执行并返回对象实例而不是 Queryset.

Subqueries need to be queries that are not immediately evaluated so that their evaluation can be postponed until the outer query is run. get() does not fit the bill as it is executed immediately and returns an object instance rather than a Queryset.

但是，将 filter 替换为 get 然后取一个 [:1] 切片应该可以:

However, substituting filter for get and then taking a [:1] slice should work:

StatByHour.objects.filter(hour_of_day=OuterRef('hour_filter')).values('hour_of_day')[:1]

注意中的字段引用如何OuterRef 是字符串文字而不是变量.

Note how the field reference in OuterRef is a string literal rather than a variable.

此外，子查询需要返回单列和单行(因为它们被分配给单个字段)，因此 values() 和上面的切片.

Moreover, subqueries need to return a single column and a single row (as they are assigned to a single field), hence the values() and the slicing above.

另外，我还没有在 Q 对象中使用子查询；我不确定它会起作用.您可能需要先将子查询输出保存在注释中，然后将其用于过滤器计算.

Also, I haven't used a subquery in a Q object yet; I'm not sure it will work. You may have to save the subquery output in an annotation first and then use that for your filter calculations.

这篇关于如何使用子查询作为 WHERE 子句的一部分编写 Django 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！