【Django】SQL文をQuerySet APIで書く方法を紹介する

# はじめに
この記事では、DjangoのQueryset APIを使ってSQL文を実行する方法を紹介します。Djangoのversion1.xに関する記事は見つけることができましたが、version 2.xに関する記事は見つけることができなかったのでまとめました。

# 環境
Mac OS Mojave 10.14.5
python 3.7.4
Django 2.2.6
MySQL 8.0.17

# 今回使用するModel
[公式ドキュメント](https://docs.djangoproject.com/en/2.2/topics/db/aggregation/)に掲載されているModelを使うことにします。

```[models.py]
from django.db import models

class Author(models.Model):
    name = models.CharField(max_length=100)
    age = models.IntegerField()

class Publisher(models.Model):
    name = models.CharField(max_length=300)

class Book(models.Model):
    name = models.CharField(max_length=300)
    pages = models.IntegerField()
    price = models.DecimalField(max_digits=10, decimal_places=2)
    rating = models.FloatField()
    authors = models.ManyToManyField(Author)
    publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)
    pubdate = models.DateField()

class Store(models.Model):
    name = models.CharField(max_length=300)
    books = models.ManyToManyField(Book)
```

# SELECT句

- 全列抽出  
実際は、\*ではなく全列がSELECT句に列挙されますが、スペースの関係上\*で表記しています(以降も同様)。

```
b = Book.objects.all()
#=> SELECT * FROM [アプリケーション名]_book;
```

- 指定列抽出  

```
b = Book.objects.values('name')
#=> SELECT [アプリケーション名]_book.name FROM [アプリケーション名]_book;
```

# WHERE句

- 条件一致  
**filter()**を使います。

```
b = Book.objects.filter(price=1000)
#=> SELECT * FROM [アプリケーション名]_book
#   WHERE [アプリケーション名]_book.price = 1000;
```

- NOT  
**exclude()**を使います。

```
b = Book.objects.exclude(price=1000)
#=> SELECT * FROM [アプリケーション名]_book
#   WHERE NOT ([アプリケーション名]_book.price = 1000);
```

- AND  
filterの引数を増やせば**AND**になります。

```
b = Book.objects.filter(price=1000, rating=5.0)
#=> SELECT * FROM [アプリケーション名]_book
#   WHERE [アプリケーション名]_book.price = 1000
#   AND [アプリケーション名]_book.rating = 5.0;
```

- OR  
**Qオブジェクト**を使用します。

```
b = Book.objects.filter(Q(price=1000) | Q(rating=5.0))
#=> SELECT * FROM [アプリケーション名]_book
#   WHERE [アプリケーション名]_book.price = 1000
#   OR [アプリケーション名]_book.rating = 5.0;
```

- LIKE  
**__contains**を使用します。

```
b = Book.objects.filter('name__contains='information')
#=> SELECT * FROM [アプリケーション名]_book
#   WHERE [アプリケーション名]_book.name LIKE 'information';
```

- BETWEEN  
**__range**を使用します。

```
import datetime
start_date = datetime.date(2019, 1, 1)
end_date = datetime.date(2019, 11, 30)
b = Book.objects.filter(pubdate__range=(start_date, end_date))
#=> SELECT * FROM [アプリケーション名]_book
#   WHERE [アプリケーション名]_book.pubdate BETWEEN '2019-01-01' and '2019-11-30';
```

- IN  
**__in**を使用します。

```
b = Book.objects.filter(id__in=[1, 3, 4])
#=> SELECT * FROM [アプリケーション名]_book
#   WHERE [アプリケーション名]_book.id IN (1, 3, 4);
```

補足：models.pyで定義されている各classではidという変数は明示的に記述されていませんが、Djangoでは定義した全てのModelにidという変数（主キーとなる）が付与されます。

# ORDER BY句

- 昇順  
**order_by()**を使用します。

```
b = Book.objects.order_by('id')
#=> SELECT * FROM [アプリケーション名]_book
#   ORDER BY [アプリケーション名]_book.id ASC;
```

- 降順  
降順キーの先頭に**-**を付けます。

```
b = Book.objects.order_by('-id')
#=> SELECT * FROM [アプリケーション名]_book
#   ORDER BY [アプリケーション名]_book.id DESC;
```

# GROUP BY句

- 全体の集約  

```
# 集計結果には明示的な名前を付けない
b1 = Book.objects.aggregate(Avg('price'))
#=> SELECT AVG([アプリケーション名]_book.price) AS price__avg FROM [アプリケーション名]_book;

# 集計結果に対し、明示的に名前をつける
b2 = Book.objects.aggregate(sum_price=Sum('price'))
#=> SELECT SUM([アプリケーション名]_book.price) AS sum_price FROM [アプリケーション名]_book;
```

この他にも**Count()**、**Max()**、**Min()**などがある。

- 行の集約  

**values().annotate()**の順で書く

```
b = Book.objects.values('publisher_id').annotate(price_avg=Avg('price'))
#=> SELECT [アプリケーション名]_book.publisher_id, 
#          AVG([アプリケーション名]_book.price) AS price_avg
#   FROM [アプリケーション名]_book
#   GROUP BY [アプリケーション名]_book.publisher_id;
```

補足：models.pyのBookクラスを見てみると、publisher_idではなくpublisherが変数として定義されている。しかし、定義をよく見ると外部キーとして定義されているので、実際にはPublisherクラスの主キーであるpublisher_idがBookテーブルのカラムとして定義されることになる。

# JOIN
**filter()**(単一行取得の場合は**get()**)を使用する方法と**select_related()**を使用する方法があります。少し複雑なので２つの方法の違いを詳しく説明します。

- **filter()**(単一行取得の場合は**get()**)を使用する場合  
下記のようにクエリを実行するとBookテーブルから行を取ってくる操作と、その外部キーを元にPublisherテーブルから行を取ってくる操作の計２回DBを叩くことになります。（こちらの手法は厳密にはJOINではありませんが、比較のために説明しています。）

```
b = Book.objects.get(id=4)  # DBを叩く
p = b.publisher        # DBを叩く
```

- **select_related()**を使用する場合  
こちらの方法では、一度のクエリでBookテーブルとPublisherテーブルの両方からオブジェクトを取得するためDBを叩く回数が少なく済みます。

```
b = Book.objects.select_related('publisher').get(id=4)
p = b.publisher        # 取得済みのオブジェクトを参照
```

ちなみに、Publisherテーブルが外部キーを持つ場合、**select_related('publisher__[外部キー]')**とすることで１度のクエリでBookテーブルの親テーブルの親テーブルまでのオブジェクトを取得することができます。

しかし、一つだけ注意しなければならないのは、このJOINはLEFT OUTER JOINに相当するということです。この例だと仮にBookテーブルが参照するPublisherテーブルの行が存在しなくても、特にエラーを吐くことなくクエリが実行されます。（存在しない部分には**None**が入ります。）

もしINNER JOINしたい場合は**exclude()**を用いて、参照先がNoneの行を除外する必要があります。（もしかしたら他に方法があるかもしれませんが、、）

# INSERT句
以下のように記述します。

```
Book(name='book1', ...).save()
#=> INSERT INTO "book" ("name", ...) VALUES ('book1', ...);
```

# UPDATE句

- **get()**による条件指定での更新  
対象のオブジェクトを取得した後、オブジェクトを更新して**save()**にてUPDATE文が実行されます。

```
b = Book.objects.get(name='book1')
b.pages += 1
b.save()
#=> UPDATE "book" SET "name" = 'book1', "pages" = 2, ...
#   WHERE [アプリケーション名]_book.id = 1;
```

\*get(name='book1')で得られたオブジェクトの主キーが'1'と仮定しています。

- **update()**による複数件の一括更新  
**update()**メソッドで、複数件の一括更新を行います。

```
Book.objects.filter(name__contains='book').update(pages=10)
#=> UPDATE "book" SET "pages" = 10
#   WHERE [アプリケーション名]_book.name LIKE 'book';
```

# DELETE句
**delete()**を使用します。

- 全件削除  

```
Book.objects.all().delete()
#=> DELETE FROM [アプリケーション名]_book;
```

- 部分削除  

```
Book.objects.filter('name__contains='information').delete()
#=> DELETE FROM [アプリケーション名]_book
#   WHERE [アプリケーション名]_book.name LIKE 'information';
```

- 外部テーブルを参照する場合  

```
Book.objects.filter(publisher__name='Bob').delete()
#=> DELETE FROM [アプリケーション名]_book
#   WHERE [アプリケーション名]_book.id IN
#   (SELECT [アプリケーション名]_book.id) FROM [アプリケーション名]_book
#   INNER JOIN [アプリケーション名]_publisher ON ([アプリケーション名]_book.publisher_id = [アプリケーション名]_publisher.id )
#   WHERE [アプリケーション名]_publisher.name = 'Bob');
```

- 親テーブルを削除する場合 

親テーブルを削除する際、デフォルトでは親テーブルのカラムを外部キーに持つ全てのオブジェクトも同時に削除されます。（[公式ドキュメント](https://docs.djangoproject.com/ja/2.2/topics/db/queries/#topics-db-queries-delete)には「デフォルトではON DELETE CASCADE制約をエミュレートします。」という記述があります。）つまり、関連する子テーブルを全て削除してから、親テーブルを削除するという順序になります。

```
publisher.objects.all().delete()
#=> DELETE FROM [アプリケーション名]_book
#   WHERE [アプリケーション名]_book.publisher_id IN 
#   (SELECT [アプリケーション名]_publisher.id FROM [アプリケーション名]_publisher);
#   DELETE FROM [アプリケーション名]_publisher;
```

テーブルをDELETEする際は、想定通りの動作となっているかを十分に検証しておいた方が良さげです。

# おわりに
今回の記事ではここまでとしますが、実際にはもっと多くのAPIが用意されています。興味のある方はDjango公式の[QuerySet API reference](https://docs.djangoproject.com/en/2.2/ref/models/querysets/)を参照してください。