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