Djangoのクエリセットでexists句を使う方法

# はじめに
タイトルの通り、Djangoのクエリセットで「exists句」を使うにはどう書けばいいか？についての調査メモ。

## Djangoにおけるクエリセットの条件指定

Djangoでクエリを発行するときは、Querysetオブジェクトに条件をパラメータとして指定する。

```py3:models.py
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='著者')
```

例えば、↑のようなモデルに対して、

```py3
Book.objects.filter(name__contains='Django', price__lte=3000)
```

↑のクエリセットなら、「書籍名に'Django'という文字列が含まれる」かつ「値段が3000以下」という条件を指定していることになり、実際に発行されるクエリは↓のような感じになる。

```
SELECT ... FROM BOOK WHERE NAME LIKE '%Django%' AND PRICE <= 3000
```

これらの条件はBookモデルのフィールドに対して直接条件を指定するものだが、ForeignKeyフィールドであれば、リレーションをたどって、リレーション先のフィールドに対する条件を書くこともできる。

```py
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のクエリセットで表現しようとすると、リレーション先のフィールドを使って、↓のように書ける。

```py3
Author.objects.filter(book__price__gte=5000)
```

…のだが、これだと「価格が5000以上の本を複数出している著者」が、その本の件数分だけヒットしてしまう。
重複を避けるためには`distinct`メソッドを使って、

```py3
Author.objects.filter(book__price__gte=5000).distinct()
```

とすればよいのだが、パフォーマンスに無駄が生じるし、何よりカッコ悪い。
こういう「◯◯が存在するなら」という条件はexists句を使えればスマートなのだが…

# 解法
というわけで色々調べてみたところ、ちゃんとexists句を使う方法があった。
（ただし、あまりスマートではない）
## (1) `extra`メソッドを使って「少しだけ」クエリを拡張する
[extraメソッド](https://docs.djangoproject.com/en/1.7/ref/models/querysets/#extra)というものが、Djangoの公式ドキュメントに載っていた。
このメソッドを使うと、select句やfrom句、where句などクエリの一部を拡張することができる。

今回はwhere句に条件を追加するので、以下のように書く

```py3
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オブジェクトなので、そのままメソッドチェーンを繋げることができる。これは便利。

```py3
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を実行する
[rawメソッドの使い方](https://docs.djangoproject.com/en/1.7/topics/db/sql/#performing-raw-queries)

こちらは何でもありのパターン。
Select句の内容を検索対象のモデルフィールドにマッチングさせることさえできていれば、SQLを全て自前で書くことができる。

例題の条件であれば、↓のように書く。

```pycon
>>> 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を直接実行する
[カスタムSQLの直接実行](https://docs.djangoproject.com/en/1.7/topics/db/sql/#executing-custom-sql-directly)

こちらは本当に何でもありのパターン。
exists句が云々というレベルで使うようなものではなく、DDLやDMLを直接発行したいときに使用する。
もしくは、対応するモデルクラスが無いクエリを発行する場合（VIEWやFUNCTIONの呼び出し等）くらいか。

あえて例題のクエリを実行するなら、↓のように書けばOK

```pycon
>>> 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つの手段がある。

 1. extraメソッド（select, from, where句を「少しだけ」拡張）
 2. rawメソッド（実行したいクエリを全て自前で書く）
 3. cursorオブジェクト（DMLやDDL等の直接実行。または対応するモデルが存在しないクエリの発行）

今回のお題である「exists句を書く」であれば、extraメソッドで十分対応が可能。
複雑なクエリを組み立てたい場合はrawメソッドを使う必要があるかもしれない。
cursorオブジェクトは、普通にアプリ作る分には、おそらく使用する機会は無いと思う。
API作ったり、複雑なデータフローが発生するようなアプリを開発する場合に出番がある…かもしれない。
（[Djangoでテーブルのコメントを生成するコマンドを作ってみた](http://qiita.com/44d/items/6efdb4050b3c4fb4c181)のときに使ったけど）

とりあえず、複雑なSQLを実行する手段も用意されている、ということでした。はい。