Как комментировать счетчик с условием в наборе запросов Django

Используя Django ORM, можно сделать что-то вроде queryset.objects.annotate(Count('queryset_objects', gte=VALUE)). Поймай мой дрейф?

Вот пример, который можно использовать для иллюстрации возможного ответа:

На веб-сайте Django создатели контента отправляют статьи и обычные пользователи просматривают (то есть читают) указанные статьи. Статьи могут быть опубликованы (т.е. Доступны для всех для чтения) или в режиме черновика. Модели, изображающие эти требования:

class Article(models.Model):
    author = models.ForeignKey(User)
    published = models.BooleanField(default=False)

class Readership(models.Model):
    reader = models.ForeignKey(User)
    which_article = models.ForeignKey(Article)
    what_time = models.DateTimeField(auto_now_add=True)

Мой вопрос: Как я могу получить все опубликованные статьи, отсортированные по уникальной аудитории за последние 30 минут? То есть Я хочу подсчитать, сколько различных (уникальных) представлений каждая опубликованная статья попала за последние полчаса, а затем составить список статей, отсортированных по этим различным представлениям.

Я пробовал:

date = datetime.now()-timedelta(minutes=30)
articles = Article.objects.filter(published=True).extra(select = {
  "views" : """
  SELECT COUNT(*)
  FROM myapp_readership
    JOIN myapp_article on myapp_readership.which_article_id = myapp_article.id
  WHERE myapp_readership.reader_id = myapp_user.id
  AND myapp_readership.what_time > %s """ % date,
}).order_by("-views")

Это вызвало ошибку: сильная синтаксическая ошибка на уровне или рядом с "01" (где "01" был объектом datetime внутри дополнительно). Это не так много.

Ответы

Ответ 1

Для Джанго> = 1,8

Использовать условное агрегирование:

from django.db.models import Count, Case, When, IntegerField
Article.objects.annotate(
    numviews=Count(Case(
        When(readership__what_time__lt=treshold, then=1),
        output_field=IntegerField(),
    ))
)

Объяснение: обычный запрос в ваших статьях будет аннотирован полем numviews. Это поле будет построено как выражение CASE/WHEN, заключенное в Count, которое будет возвращать 1 для критериев соответствия читателей и NULL для критериев совпадения читателей. Счетчик будет игнорировать нули и считать только значения.

Вы получите нули для статей, которые недавно не просматривались, и вы можете использовать это поле numviews для сортировки и фильтрации.

Запрос для этого для PostgreSQL будет:

SELECT
    "app_article"."id",
    "app_article"."author",
    "app_article"."published",
    COUNT(
        CASE WHEN "app_readership"."what_time" < 2015-11-18 11:04:00.000000+01:00 THEN 1
        ELSE NULL END
    ) as "numviews"
FROM "app_article" LEFT OUTER JOIN "app_readership"
    ON ("app_article"."id" = "app_readership"."which_article_id")
GROUP BY "app_article"."id", "app_article"."author", "app_article"."published"

Если мы хотим отслеживать только уникальные запросы, мы можем добавить различие в Count и сделать наше предложение When для возврата значения, которое мы хотим различить.

from django.db.models import Count, Case, When, CharField, F
Article.objects.annotate(
    numviews=Count(Case(
        When(readership__what_time__lt=treshold, then=F('readership__reader')), # it can be also 'readership__reader_id', it doesn't matter
        output_field=CharField(),
    ), distinct=True)
)

Это даст:

SELECT
    "app_article"."id",
    "app_article"."author",
    "app_article"."published",
    COUNT(
        DISTINCT CASE WHEN "app_readership"."what_time" < 2015-11-18 11:04:00.000000+01:00 THEN "app_readership"."reader_id"
        ELSE NULL END
    ) as "numviews"
FROM "app_article" LEFT OUTER JOIN "app_readership"
    ON ("app_article"."id" = "app_readership"."which_article_id")
GROUP BY "app_article"."id", "app_article"."author", "app_article"."published"

Для django <1.8 и PostgreSQL

Вы можете просто использовать raw для выполнения оператора SQL, созданного новыми версиями django. По-видимому, не существует простого и оптимизированного метода для запроса этих данных без использования raw (даже с extra есть некоторые проблемы с введением требуемого предложения JOIN).

Articles.objects.raw('SELECT'
    '    "app_article"."id",'
    '    "app_article"."author",'
    '    "app_article"."published",'
    '    COUNT('
    '        DISTINCT CASE WHEN "app_readership"."what_time" < 2015-11-18 11:04:00.000000+01:00 THEN "app_readership"."reader_id"'
    '        ELSE NULL END'
    '    ) as "numviews"'
    'FROM "app_article" LEFT OUTER JOIN "app_readership"'
    '    ON ("app_article"."id" = "app_readership"."which_article_id")'
    'GROUP BY "app_article"."id", "app_article"."author", "app_article"."published"')

Ответ 2

Для django> = 2.0 вы можете использовать условное агрегирование с аргументом filter в агрегатных функциях:

from datetime import timedelta
from django.utils import timezone
from django.db.models import Count, Q # need import

Article.objects.annotate(
    numviews=Count(
        'readership__reader__id', 
        filter=Q(readership__what_time__gt=timezone.now() - timedelta(minutes=30)), 
        distinct=True
    )
)