Блог

Я использую Django и Python 3.7. У меня возникли проблемы с пониманием того, как написать запрос Django, в котором есть подзапрос как часть предложения where . Вот модели …

 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 запрос будет выглядеть примерно так

 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

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

Итак, я подумал, что мог бы сделать что-то вроде этого…

 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 amp; votes_criterion2,
                                  comments__lte=25)
  

но это приводит к «Не удается разрешить ключевое слово ‘article’ в поле. Возможны следующие варианты: ошибка hour_of_day, id, index, num_articles, total_score». Я думаю, это связано с тем, что Django выполняет мой запрос «StatByHour.objects» перед выполнением более крупного запроса внутри него, но я не знаю, как переписать вещи, чтобы заставить подзапрос выполняться одновременно.

Редактировать: K, переместил мой подзапрос в фактическую функцию «Подзапрос» и сослался на фильтр, который я создал с помощью 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 amp; votes_criterion2,
                                  comments__lte=25)
  

и это приводит к

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

что странно, потому что я использую его в подзапросе.

Редактировать # 2: даже после изменения запроса в соответствии с заданным ответом…

 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 amp; et_criterion2 amp; et_criterion3,
                                  votes_criterion1 amp; votes_criterion2,
                                  article__front_page_first_appeared_date__isnull=True,
                                  comments__lte=25)
  

Я все еще получаю ошибку

 'Func' object has no attribute 'split'
  

Подзапросы должны быть запросами, которые не оцениваются немедленно, чтобы их оценку можно было отложить до запуска внешнего запроса. get() не соответствует требованиям, поскольку он выполняется немедленно и возвращает экземпляр объекта, а не Queryset .

Однако замена filter get , а затем взятие [:1] фрагмента должны работать:

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

Обратите внимание, что ссылка на поле в OuterRef является строковым литералом, а не переменной.

Более того, подзапросы должны возвращать один столбец и одну строку (поскольку они назначены одному полю), отсюда values() и приведенное выше срезание.

Кроме того, я еще не использовал подзапрос в Q объекте; я не уверен, что это сработает. Возможно, вам придется сначала сохранить вывод подзапроса в аннотации, а затем использовать его для вычислений фильтра.

Используйте фильтрацию по подзапросу, который сам фильтруется hour_of_day=ExtractHour(OuterRef('article__created_on') timedelta(seconds=avg_fp_time_in_seconds)) . Реальный код потребует одного дополнительного ExpressionWrapper и работает только на Django >= 2.1.0 :

 import datetime

from django.db import models
from django.db.models import F, OuterRef, Subquery, Value
from django.db.models.functions import ExtractHour, Coalesce
from django.db.models.expressions import ExpressionWrapper


relevant_hour_stats = (
    StatByHour.objects
    .filter(
        hour_of_day=ExtractHour(ExpressionWrapper(
            OuterRef('article__created_on')  # NOTE: `OuterRef() Expression` works only on Django >= 2.1.0
             
            datetime.timedelta(seconds=avg_fp_time_in_seconds),
            output_field=models.DateTimeField()
        )),
    )
    .annotate(
        votes_threshold=Coalesce(
            100.0 * F('index'),
            0.0,
            output_field=models.FloatField(),
        ),
    )
    .order_by('-votes_threshold')
    # NOTE: your StatByHour model does not have unique=True on hour_of_day
    # field, so there may be several stat for same hour.
    # And from your SQL example it's unclear how should they be handled. So I
    # assume that "greatest" threshold is needed.
)

article_stats = (
    ArticleStat.objects
    .all()
    .filter(
        votes__gt=Coalesce(
            Subquery(relevant_hour_stats.values('votes_threshold')[:1]),
            Value(0.0),
            output_field=models.FloatField(),
        ),
    )
)

  

PS Было бы намного проще, если бы вы настроили какой-нибудь «демонстрационный проект» на github, чтобы любой мог его клонировать и проверять свои идеи локально.

P.P.S. Этот код проверен на работоспособность, но на разных моделях / полях:

 In [15]: relevant_something = (ModelOne.objects.filter(index=ExtractHour(ExpressionWrapper(OuterRef('due_date')   datetime.timedelta(seconds=1000), output_field=models.DateTimeField()))).annotate(votes_threshold=100*F('indent')).order_by('-votes_threshold'))

In [16]: ts = ModelTwo.objects.all().filter(votes__gt=Subquery(relevant_notes.values('votes_threshold')[:1], output_field=models.IntegerField()))

In [17]: print(ts.query)
  

 SELECT 
    ...
FROM 
    "some_app_model_two" 
WHERE 
    "some_app_model_two"."votes" > (
        SELECT 
            (100 * U0."indent") AS "votes_threshold" 
        FROM 
            "some_app_model_one" U0 
        WHERE 
            U0."index" = (
                EXTRACT(
                    'hour' 
                    FROM ("some_app_model_two"."due_date"   0:16:40) 
                    AT TIME ZONE 'America/Los_Angeles'
                )
            ) 
        ORDER BY "votes_threshold" DESC 
        LIMIT 1
    )
ORDER BY 
    "some_app_model_two"."due_date" ASC, 
    "some_app_model_two"."priority" ASC, 
    "some_app_model_two"."updated_at" DESC
  

Поэтому, если вы получаете какие-либо ошибки с ним, пожалуйста, покажите ФАКТИЧЕСКИЙ код, который вы выполняете

Я считаю полезным как можно больше переходить к аннотациям, чтобы уточнить, что происходит.

Вы можете использовать Extract функцию для получения часа. Если вы хотите включить более сложный avg_fp_time_in_seconds материал, вам нужно будет определить свой собственный Func , который я не пытался дублировать, поскольку он заслуживает отдельного сообщения (возможно, именно отсюда 'Func' object has no attribute 'split' возникла ошибка).

 # First, add a field for the hour 
articles_with_hour = Article.objects.annotate(created_on_hour=ExtractHour('created_on'))

# Set up the subquery, referencing the annotated field
for_this_hour = StatByHour.objects.filter(hour_of_day=OuterRef('created_on_hour'))

# Add the subquery, making sure to slice down to one value
articles_with_hour_index = articles_with_hour.annotate(
    index_for_this_hour=Subquery(for_this_hour.values('index')[:1]),
)

# Add the website averages for later calculations 
#  (note if total_score and num_articles are different field types
#  you may need an ExpressionWrapper)
articles_with_avg_website_score = articles_with_hour_index.annotate(
    average_article_score_for_website=(
        F("website__stats__total_score") / F("website__stats__num_articles")
    )
)

# Use the averages to calculate the trending floor for each article
articles_with_trending_floor = articles_with_avg_website_score.annotate(
    trending_floor=F('average_article_score_for_website') * settings.TRENDING_PCT_FLOOR,
)

# Set up the criteria, referencing fields that are already annotated on the qs
# ...
votes_gte_trending_floor_for_this_hour_criterion = Q(articlestats__votes__gte=(
    F('trending_floor')
    * F('index_for_this_hour')
    * day_of_week_index  # not sure where this comes from?
))
# ...

# Then just filter down (note this is an Article QuerySet, not ArticleStat)
qset = articles_with_trending_floor.filter(
    votes_gte_trending_floor_for_this_hour_criterion,
    # other criteria
    front_page_first_appeared_date__isnull=True,
    articlestats__comments__lte=25,
)

  

Многие из этих вычислений можно было бы сжать, и, возможно, даже можно было бы сделать все за один annotate вызов, используя несколько kwargs, но я думаю, что изложение всего этого облегчает понимание.

Это действительно похоже на решение подзапроса.

Django >= 1.11

В качестве предупреждения я протестировал код, но только с моделями, у меня не было никаких данных, поэтому этот ответ — просто попытка указать вам правильное направление

 # Query that references an outer field from another model, in this case created_on.
# On wich we are performing a lookup in order to "extract" the hour (assuming here)
# a DateTimeField or a TimeField.
stat_by_hour = StatByHour.objects.filter(hour_of_day=OuterRef('created_on__hour'))


# Then filter articles, that have articlestats.votes 
# greater than 100 * stat_by_hour.index
result = Article.objects.filter(
    articlestats__votes__gt=100 * Subquery(stat_by_hour.values('index')[:1], output_field=FloatField())
)
  

На первый взгляд кажется, что вам может потребоваться выполнить order_by('index') или order_by('-index') в вашем подзапросе, таким образом, фрагмент [:1] получит минимальное или максимальное значение (в зависимости от ваших потребностей).)

Я уверен, что вы можете использовать это (или что-то очень похожее) для достижения того, чего вы хотите.

Взгляните на запросы Django. Я думаю, вы могли бы решить проблему, изменив базовые запросы SQL на то, что предлагает Django.

Если это не работает, вы можете выполнять необработанные SQL-запросы.