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
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)
)
)
)
print(sqlparse.format(str(categories.query), reindent=True))
組み立てた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
