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


参考)

http://djangoproject.jp/doc/ja/1.0/ref/models/querysets.html#extra-select-none-where-none-params-none-tables-none-order-by-none-select-params-none