DjangoのQuerySetで条件付きのCountをする
先日、以下のモデルとデータで、「Publisherごとにratingが3.0以上のBookの数をカウントしたい」場合の取得方法を調べたのだが、
Bookレコードを1つも持たないPublisherのデータがある場合、レコードが返されないことに気がついた。
class Publisher(models.Model): name = models.CharField(max_length=300) class Book(models.Model): isbn = models.CharField(max_length=9) name = models.CharField(max_length=300) price = models.DecimalField(max_digits=10, decimal_places=2) rating = models.FloatField() publisher = models.ForeignKey(Publisher)
[{"pk": 1, "model": "books.publisher", "fields": {"name": "test1"}}, {"pk": 2, "model": "books.publisher", "fields": {"name": "test2"}}, {"pk": 3, "model": "books.publisher", "fields": {"name": "test3"}}, {"pk": 1, "model": "books.book", "fields": {"rating": 5.0, "price": "2000", "isbn": "12345", "name": "book1", "publisher": 1}}, {"pk": 2, "model": "books.book", "fields": {"rating": 2.0, "price": "3500", "isbn": "12346", "name": "book2", "publisher": 1}}, {"pk": 3, "model": "books.book", "fields": {"rating": 4.0, "price": "2400", "isbn": "12347", "name": "book3", "publisher": 2}}, {"pk": 4, "model": "books.book", "fields": {"rating": 5.0, "price": "3800", "isbn": "12348", "name": "book4", "publisher": 2}} ]
先日調べた、annotateとfilterの組み合わせ
Publisher.objects.filter(book__rating__gt=3.0).annotate(num_books=Count('book'))
以下のようなSQLが発行されるが、これだと、publisherに関連するbookレコードが一つもない場合、レコード自体返ってこない
SELECT "books_publisher"."id", "books_publisher"."name", COUNT("books_book"."id") AS "num_books" FROM "books_publisher" LEFT OUTER JOIN "books_book" ON ("books_publisher"."id" = "books_book"."publisher_id") WHERE "books_book"."rating" > 3.0 GROUP BY "books_publisher"."id", "books_publisher"."name";
結果
| id | name | num_books |
|---|---|---|
| 1 | test1 | 1 |
| 2 | test2 | 2 |
LEFT OUTER JOINに ratingの条件をANDで付加できれば、bookレコードが0件のpublisherも取得することができるのだが、DjangoのQuerySetでこのようなSQLを生成することができるだろうか？
SELECT "books_publisher"."id", "books_publisher"."name", COUNT("books_book"."id") AS "num_books" FROM "books_publisher" LEFT OUTER JOIN "books_book" ON ("books_publisher"."id" = "books_book"."publisher_id") AND "books_book"."rating" > 3.0 GROUP BY "books_publisher"."id", "books_publisher"."name";
| id | name | num_books |
|---|---|---|
| 1 | test1 | 1 |
| 2 | test2 | 2 |
| 3 | test3 | 0 |
ちょっと調べてみた感じだと、LEFT OUTER JOINに条件を追加する方法はなさそうだったので、extraを使ってやってみた
Publisher.objects.extra(select={'num_books': 'select count(*) from books_book where books_book.publisher_id = books_publisher.id and books_book.rating > 3.0'})
発行されるSQL
SELECT (select count(*) from books_book where books_book.publisher_id = books_publisher.id and books_book.rating > 3.0) AS "num_books", "books_publisher"."id", "books_publisher"."name" FROM "books_publisher";
これで一応期待した結果を取得できた
| num_books | id | name |
|---|---|---|
| 1 | 1 | test1 |
| 2 | 2 | test2 |
| 0 | 3 | test3 |
参考)
