はじめに
タイトルの通り、Djangoのクエリセットで「exists句」を使うにはどう書けばいいか？についての調査メモ。
Djangoにおけるクエリセットの条件指定
Djangoでクエリを発行するときは、Querysetオブジェクトに条件をパラメータとして指定する。
from django.db import models
class Author(models.Model):
class Meta :
db_table = 'AUTHOR'
name = models.CharField('著者名', max_length=100)
birthday = models.DateField('生年月日')
class Book(models.Model):
class Meta :
db_table = 'BOOK'
name = models.CharField('書籍名', max_length=100)
price = models.IntegerField('価格')
author = models.ForeignKey(Author, verbose_name='著者')
例えば、↑のようなモデルに対して、
Book.objects.filter(name__contains='Django', price__lte=3000)
↑のクエリセットなら、「書籍名に'Django'という文字列が含まれる」かつ「値段が3000以下」という条件を指定していることになり、実際に発行されるクエリは↓のような感じになる。
SELECT ... FROM BOOK WHERE NAME LIKE '%Django%' AND PRICE <= 3000
これらの条件はBookモデルのフィールドに対して直接条件を指定するものだが、ForeignKeyフィールドであれば、リレーションをたどって、リレーション先のフィールドに対する条件を書くこともできる。
Book.objects.filter(author__name__contains='xxx')
↑の場合は、FKを張っているカラムを結合条件に使って、↓のようなクエリになる。
SELECT ... FROM BOOK INNER JOIN AUTHOR ON (BOOK.AUTHOR_ID = AUTHOR.ID)
WHERE AUTHOR.NAME LIKE '%xxx%'
なお、テーブルの結合はFKフィールドからリレーションをたどってDjango側で自動的に生成しているものであり、FKを張っていないフィールドを結合条件として自前でテーブル結合を行うことはできない。
exists句の構文
SQLにおけるEXISTS句は、Where句の中で以下のように使用する。
SELECT ... FROM AUTHOR
WHERE EXISTS(
SELECT 1 FROM BOOK
WHERE BOOK.AUTHOR_ID = AUTHOR.ID
AND BOOK.PRICE >= 5000
)
この例では、「価格が5000以上の本を出している著者を検索する」というSQLになっている。
これをDjangoのクエリセットで表現しようとすると、リレーション先のフィールドを使って、↓のように書ける。
Author.objects.filter(book__price__gte=5000)
…のだが、これだと「価格が5000以上の本を複数出している著者」が、その本の件数分だけヒットしてしまう。
重複を避けるためにはdistinctメソッドを使って、
Author.objects.filter(book__price__gte=5000).distinct()
とすればよいのだが、パフォーマンスに無駄が生じるし、何よりカッコ悪い。
こういう「◯◯が存在するなら」という条件はexists句を使えればスマートなのだが…
解法
というわけで色々調べてみたところ、ちゃんとexists句を使う方法があった。
（ただし、あまりスマートではない）
(1) extraメソッドを使って「少しだけ」クエリを拡張する
extraメソッドというものが、Djangoの公式ドキュメントに載っていた。
このメソッドを使うと、select句やfrom句、where句などクエリの一部を拡張することができる。
今回はwhere句に条件を追加するので、以下のように書く
Author.objects.extra(where=['exists(select 1 from BOOK where BOOK.author_id=AUTHOR.id and BOOK.price >= 5000)'])
↑の内容で発行されるSQLは↓のようになる。
SELECT "AUTHOR"."id", "AUTHOR"."name", "AUTHOR"."birthday" FROM "AUTHOR"
WHERE (exists(select 1 from BOOK where BOOK.author_id=AUTHOR.id and BOOK.price >= 5000))
extraで指定した文字列が、そのままwhere句の条件として追加される。
なお、extraで追加するwhereパラメータはリスト型であり、複数の条件を指定すると、自動的にANDで結合してくれる。
ちなみに、from句とwhere句を併用してFKを張っていないテーブルを結合する、といったことも可能。応用範囲は広い。
それでいて戻り値はQuerysetオブジェクトなので、そのままメソッドチェーンを繋げることができる。これは便利。
Author.objects.extra(where=['exists(...省略...)']).order_by('birthday')
↓こんな感じになる
SELECT "AUTHOR"."id", "AUTHOR"."name", "AUTHOR"."birthday" FROM "AUTHOR"
WHERE (exists(select 1 from BOOK where BOOK.author_id=AUTHOR.id and BOOK.price >= 5000))
ORDER BY "AUTHOR"."birthday" ASC
(2) rawメソッドを使って素のSQLを実行する
こちらは何でもありのパターン。
Select句の内容を検索対象のモデルフィールドにマッチングさせることさえできていれば、SQLを全て自前で書くことができる。
例題の条件であれば、↓のように書く。
>>> Author.objects.raw('''select id,name,birthday from AUTHOR
... where exists(
... select 1 from BOOK
... where BOOK.author_id=AUTHOR.id
... and BOOK.price >= 5000)''')
...
<RawQuerySet: 'select id,name,birthday from AUTHOR where exists(select 1 from BOOK where BOOK.author_id=AUTHOR.id and BOOK.price >= 5000)'>
なお、rawメソッドの戻りはRawQuerySetオブジェクトのため、Querysetのメソッドチェーンを繋げることはできない。
まあ、SQL全て自前で書いているのだから、あえてメソッドチェーンで書く必要は無いけど。
(3) cursorオブジェクトを使ってカスタムSQLを直接実行する
こちらは本当に何でもありのパターン。
exists句が云々というレベルで使うようなものではなく、DDLやDMLを直接発行したいときに使用する。
もしくは、対応するモデルクラスが無いクエリを発行する場合（VIEWやFUNCTIONの呼び出し等）くらいか。
あえて例題のクエリを実行するなら、↓のように書けばOK
>>> from django.db import connection
>>> cursor = connection.cursor()
>>> cursor.execute('select id,name,birthday from AUTHOR where exists(...省略...)')
>>> cursor.fetchall()
[(1,'xxx',datetime.date(yyyy,mm,dd)), (2,'xxx',datetime.date(yyyy,mm,dd)),,,]
結果は、タプルのリストとなる。
dictですらないので、cursor経由でフェッチした場合は注意が必要。
まとめ
Djangoのfilterなどでどうしても表現できないクエリを書きたい場合は、以下の3つの手段がある。
- extraメソッド（select, from, where句を「少しだけ」拡張）
- rawメソッド（実行したいクエリを全て自前で書く）
- cursorオブジェクト（DMLやDDL等の直接実行。または対応するモデルが存在しないクエリの発行）
今回のお題である「exists句を書く」であれば、extraメソッドで十分対応が可能。
複雑なクエリを組み立てたい場合はrawメソッドを使う必要があるかもしれない。
cursorオブジェクトは、普通にアプリ作る分には、おそらく使用する機会は無いと思う。
API作ったり、複雑なデータフローが発生するようなアプリを開発する場合に出番がある…かもしれない。
（Djangoでテーブルのコメントを生成するコマンドを作ってみたのときに使ったけど）
とりあえず、複雑なSQLを実行する手段も用意されている、ということでした。はい。