Django のクエリセットで Count を条件付きでアノテートする方法

質問

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分からのユニークな読者によって並べられたすべての公開記事を取得することができますか？すなわち、私は、各公開記事が過去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" は extra 内の datetime オブジェクトでした)。大したことではありません。

どのように解決するのですか？

djangoの場合 >= 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 を返します。CountはNULLを無視し、値のみをカウントします。

最近閲覧されていない記事には0が表示されるので、それを利用することができます。 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 を使えば、新しいバージョンの django が作成した SQL 文を実行することができます。を使わずにそのデータを照会するための、単純で最適化された方法はないようです。 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"')