Annotations across multiple tables with Django 1.8 ORM

Question

I'm having issues with the new

Let's say I have the following 3 models:

class Author(models.Model):
    name = models.TextField()

class Book(models.Model):
    author = models.ForeignKey(Author, related_name='books')
    title = models.TextField()
    is_fiction = models.BooleanField()

class SpeakingEvent(models.Model):
    author = models.ForeignKey(Author, related_name='events')
    date = models.DateField()

And I wanted to get a single queryset of Authors annotated with the number of fiction books published, and the number of future speaking events.

So, I can do the following:

Author.objects.all().annotate(
    future_speaking=Sum(Case(
        When(events__date__gte=timezone.now(),
             then=1),
        default=0)),
    fiction_count=Sum(Case(
        When(books__is_fiction=True,
             then=1),
        default=0)),
)

However, the resulting numbers are too high. If there are m books and n events, then we I end up the <correct_result> * m for the event count, <correct_result> * n for the book count. This is caused by the way that django joins the two tables together with a left join. The results are correct if I leave out either of the annotations.

Is there any way to either change the joins are remove the duplicates with a when clause?

Answer 1

What I ended up doing is basically counting along with it, and dividing in code.

Author.objects.all().annotate(
future_speaking_divide_count=Count('events'),
future_speaking=Sum(Case(
    When(events__date__gte=timezone.now(),
         then=1),
    default=0)),
fiction_count=Sum(Case(
    When(books__is_fiction=True,
         then=1),
    default=0)),
fiction_count=Count('books')
)