この記事は、 JSL(日本システム技研) Advent Calendar 2020 - Qiita 12/3の記事です。
以前、SQLをDjangoのQuerySet APIでどう実装するのかを書きました。
- SQLのSELECT文を、DjangoのQuerySet APIで書いてみた - メモ的な思考的な
- SQLのINSERT, UPDATE文を、DjangoのQuerySet APIで書いてみた - メモ的な思考的な
- SQLのDELETE文を、DjangoのQuerySet APIで書いてみた - メモ的な思考的な
上記ではサブクエリについてはふれなかったのですが、当時EXISTS句を使うのは大変だった記憶があります。
そんな中、EXISTS句を使う機会があったため、Django3系ではどうなっているのかメモに残します。
目次
環境
やりたいこと
ColorとAppleモデルがあり、Color : Apple = 1 : 多 の関係とします。
class Color(models.Model): name = models.CharField('名前', max_length=20) class Apple(models.Model): name = models.CharField('名前', max_length=20) color = models.ForeignKey('Color', on_delete=models.PROTECT)
この時、Appleが存在するColorを全件抽出したいとします。
SQL的にはこんな感じ。
SELECT "sql_exists_color"."id", "sql_exists_color"."name" FROM "sql_exists_color" WHERE EXISTS( SELECT U0."id", U0."name", U0."color_id" FROM "sql_exists_apple" U0 WHERE U0."color_id" = "sql_exists_color"."id" )
Django3系でのやり方
改めて調べたところ、Django3系では以前より直感的に書けるようになっていました。
- クエリー式 | Django ドキュメント | Django
- Conditional Expressions | Django ドキュメント | Django
- Django 3.0主な変更点まとめ - ryu22eBlog
- In a Django QuerySet, how to filter for "not exists" in a many-to-one relationship - Stack Overflow
上記のように、「Appleが存在するColorを全件抽出したい」場合は
Color.objects.filter(
Exists(Apple.objects.filter(color=OuterRef('pk')))
)
とSQLと似たような形で書けるようになっていました。
Exists() の中にサブクエリを書き、Exists中のfilterにて、 OuterRef でサブクエリの外のフィールドを参照・比較します。
テストコードでの確認
テストコードで確認してみます。
AppleFactoryではColorが緑のものは生成しないようにし、self.assertListEqual(list(colors), [yellow, red]) にて期待通りの結果になるかを確認します。
from django.conf import settings from django.db import connection from django.test import TestCase from django.db.models import Exists, OuterRef from sql_exists.factories import ColorFactory, AppleFactory from sql_exists.models import Color, Apple class TestM2MExists(TestCase): def test_exists(self): ColorFactory(name='緑') yellow = ColorFactory(name='黄') red = ColorFactory(name='赤') AppleFactory(name='シナノゴールド', color=yellow) AppleFactory(name='シナノスイート', color=red) AppleFactory(name='フジ', color=red) AppleFactory(name='シナノドルチェ', color=red) colors = Color.objects.filter( Exists(Apple.objects.filter(color=OuterRef('pk'))) ) # テストコードでは常にDEBUG=Falseになり、connection.queriesが取得できないことから強制書き換え # なお、今回の場合SQLが発行されるのは、list()のタイミング # ちなみに、Django1.11からはmanage.pyのオブションに `--debug-mode` もある # https://docs.djangoproject.com/en/3.1/ref/django-admin/#cmdoption-test-debug-mode settings.DEBUG = True self.assertListEqual(list(colors), [yellow, red]) for query in connection.queries: print(query) settings.DEBUG = False
テストを実行すると、以下の通りパスしました。
$ python manage.py test sql_exists
Creating test database for alias 'default'...
System check identified no issues (0 silenced).
{'sql': 'SELECT "sql_exists_color"."id", "sql_exists_color"."name" FROM "sql_exists_color" WHERE EXISTS(SELECT U0."id", U0."name", U0."color_id" FROM "sql_exists_apple" U0 WHERE U0."color_id" = "sql_exists_color"."id")', 'time': '0.000'}
.
----------------------------------------------------------------------
Ran 1 test in 0.009s
OK
Destroying test database for alias 'default'...
ソースコード
Githubに上げました。
https://github.com/thinkAmi-sandbox/django_31-sample
