DjangoのSubqueryとOuterRefを使ってサブクエリを組み立てる

DjangoのORMで、サブクエリを使う方法について。任意のSQLであればrawメソッドを使えばよいのですが、なるべくORMのAPIを使いたい。

DjangoのORMでは任意の位置にサブクエリを使えるわけではないですが、例えば「テーブル単位での問い合わせ結果にサブクエリで得た列を追加する」ぐらいのことは、annotateメソッドを使ってできます。

サブクエリ側で条件を指定した絞り込みを行う場合、SubqueryとOuterRefを組み合わせるとできます。

Query Expressions | Django documentation | Django

モデル

Categoryモデルを親に持つ、Itemモデル、という関係性のデータです。

from django.db import models
from django.utils import timezone


class Category(models.Model):
    parent = models.ForeignKey(
        'myapp.Category', null=True, blank=True,
        on_delete=models.CASCADE)
    name = models.CharField(max_length=20)

    class Meta:
        db_table = 'category'
        ordering = ['id']

    def __str__(self):
        return self.name


class Item(models.Model):
    category = models.ForeignKey(
        'myapp.Category', null=True, blank=True,
        on_delete=models.SET_NULL)
    name = models.CharField(max_length=20)
    updated = models.DateTimeField(default=timezone.now)

    class Meta:
        db_table = 'item'
        ordering = ['id']

    def __str__(self):
        return self.name

Djangoのシェルで試したコード

CountなどのAggregate Expressionでうまくクエリを作れない場合、Subqueryを継承すると、自由にクエリを作れます。

サブクエリの結果をCountするCountQueryクラスを定義して使ってます。

from datetime import timedelta
from django.utils import timezone
from django.db.models import OuterRef, Subquery, IntegerField
import sqlparse
from myapp.models import Category, Item

class CountQuery(Subquery):
    """件数をカウントするサブクエリ
    """
    template = "(SELECT COUNT(*) FROM (%(subquery)s) _count)"
    output_field = IntegerField()

categories = Category.objects.annotate(
    updated_count=CountQuery(
        Item.objects.filter(
            category_id=OuterRef('pk'),
            updated__gt=timezone.now() - timedelta(days=1)  # 24時間以内に更新されたItemの件数を取得
        )
    )
)

print(sqlparse.format(str(categories.query), reindent=True))  # sqlparseでSQL文を整形

組み立てたSQLは、sqlparseで整形して表示してみてます。

SELECT "category"."id",
       "category"."parent_id",
       "category"."name",

  (SELECT COUNT(*)
   FROM
     (SELECT U0."id",
             U0."category_id",
             U0."name",
             U0."updated"
      FROM "item" U0
      WHERE (U0."category_id" = ("category"."id")
             AND U0."updated" > 2019-03-19 08:22:03.638946)
      ORDER BY U0."id" ASC) _count) AS "updated_count"
FROM "category"
ORDER BY "category"."id" ASC

理想はもう1段減らしたいのだけど、きれいに作る方法を見つけれてないです...

参考

Django 1.11 Annotating a Subquery Aggregate - Stack Overflow