Complex count across many to many field in Django ORM

So I have a set of tasks that can appear in many categories:

class TaskGroup(models.Model):
    name = models.CharField(max_length=200)
    slug = models.SlugField(max_length=200)
    icon = models.CharField(max_length=200, blank=True, null=True)

    def __unicode__(self):
        return unicode(self.name)


class Task(models.Model):
    start_date = models.DateField()
    end_date = models.DateField()
    is_date_fuzzy = models.BooleanField()
    name = models.CharField(max_length=200)
    assignee = models.ForeignKey(User, verbose_name="users who is assigned the task", blank=True, null=True)
    task_groups = models.ManyToManyField(TaskGroup)

As you can see, each task can appear in multiple task groups.

I would like the following conditions to be satisfied by my query:

1. The list of all TaskGroups should be returned.
2. A count of the number of Tasks within a specific group. I.e. Furniture (3), Bedding (2), Floor lamps (6)
3. If there are no tasks for a specific TaskGroup, said group should have 0
4. The tasks within each group are limited to by the current user.

The best I have come up with so far is something like this:

TaskGroup.objects.filter(
    task__assignee=current_usr
).annotate(
    task_count=Count('task__id')
).order_by('name')

But it fil开发者_开发百科ters everything before doing the count, so I don't see the task groups with zero tasks.

Maybe I'm being very think but I've been trying to do this for ages and I'm at this stage just tempted to loop through and do the count myself.

I really hope you can help save what little remains of my sanity!

I had the exact same problem once. One solution would be to use an extra subquery:

TaskGroup.objects.extra(
    select={"task_count": "SELECT COUNT(*) from app_task where app_task.id=app_taskgroup.task_id AND app_task.assignee = '?'"},
    select_params = [current_usr]
).order_by('name')

Or something like that. Probably the joining key is wrong.

But this is just ugly. and breaks a lot of DRY and DB independance priciples.

A better option would probably be to use 2 queries. The second one for the particular Groups that don't have any tasks assigned to that user:

empty_groups = TaskGroup.objects.exclude(task__assignee=current_usr)

Then just iterate over the two sets instead of only one.