Django3系のORMでSQLのEXISTS句を使う

Django Python

この記事は、 JSL(日本システム技研) Advent Calendar 2020 - Qiita 12/3の記事です。

以前、SQLを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系では以前より直感的に書けるようになっていました。

　
上記のように、「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