如何编写带有子查询的 Django 查询作为 WHERE 子句的一部分? [英] 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)

但这会导致无法将关键字 'article' 解析为字段.选项包括: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.

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