Using Django annotate(Count) with a nullable ForeignKey

Question

Using the example Models from https://docs.djangoproject.com/en/dev/topics/db/queries/

I want to do something like the following:

q = Entry.objects.filter(...)
entries = q.all().order_by('pub_date')
blog_counts = q.values('blog__name').annotate(Count('blog'))

This gives me back a result like the following:

[{'blog__count': 3, 'blog__name': u'Cheddar Talk'}, {'blog__count': 5, 'blog__name': u'Beatles Blog'}]

If the Entry.blog ForeignKey is made "null=True, blank=True", and I create several Entry records with a null value for blog, I get something like this:

[{'blog__count: 0, 'blog__name': None}, {'blog__count': 3, 'blog__name': u'Cheddar Talk'}, {'blog__count': 5, 'blog__name': u'Beatles Blog'}]

I expected to get the actual number of entries with no Blog associated, not 0. Looking at the SQL executed, I see that it is doing COUNT(blog.id) instead of something like COUNT(entry.blog_id), but changing to .annotate(Count('blog_id')) gives me:

FieldError: Cannot resolve keyword 'blog_id' into field.

What can I do to get an accurate count of Entries without an associated Blog?

Answer 1

The generated SQL statement uses count clause in the form of Count(<field_name>) which does not count nulls. Unfortunately (at least MySQL and PostgreSQL) backends include null values into output even though nulls are not counted! Which leaves the user with a false impression that there are no null values at all.

Solution

The generated SQL statement must be in the form Count(*) instead of Count(<field_name>). This will result in correct count of nulls.

blog_counts = q.values('blog__name').annotate(Count('*'))

See ticket:

https://code.djangoproject.com/ticket/15183

Answer 2

Entry.objects.filter(blog__isnull=True).count()

That will give you what you want. In your current code, Count is giving you exactly what you asked for, the number of blogs each entry is associated with. In the case of the ones with null blogs, that's zero, obviously.