Django: Filtering Annotated Results

Question

I have two models:

class Status(models.Model):
    title = models.CharField(max_length=32)

class Task(models.Model):
    user = models.ForeignKey(User)
    status = models.ForeignKey(Status, default=1)
    title = models.CharField(max_length=128)

I want to create a nav list that contains all of the statuses I have in my status model, example: Today, Tomorrow, Waiting, Scheduled, Trash

Simple enough. I then want to display the number of tasks assigned to each status, thanks to SO, also simple:

Status.objects.all().annotate(Count('task'))

This nicely creates a list of all of my statuses and a number the number of tasks assigned to each:

Today (1) Tomorrow (1) Waiting (0) Scheduled (2) Trash (7)

The trick with all of this is now how to filter the above values so that they only reflect the current logged in user. Adding a filter to the queryset seems to remove any zero statuses which makes sense. I want those zero statuses though. My current idea involves Q():

Status.objects.filter(Q(task__user=1) | Q(task__user__isnull=True)).annotate(Count('task'))

This does not work.

Any ideas?

Edit for Yuji

Status.objects.all().annotate(Count('task'))

Gives:

Inbox (3) Today (0) Next (1) Waiting (0) Scheduled (1) Later (0) Someday (0) Archives (0) Trash (0)

Great but 1 of those inbox tasks and scheduled are for another user. Ok, so let's try filtering.

Status.objects.filter(task__user=current_user).annotate(Count('task'))

Inbox (2) Next (1)

Works! Sorta.... My (as I called them) zero'ed out statuses aren't there. I should say, any status that doesn't have a task associated with it as that currently logged in user does not show up. I want it to show up.

Answer 1

This should give you a count of all Task objects belonging to the User by count.

Status.objects.filter(task__user=current_user).annotate(Count('task'))

What do you mean adding a filter removes any zero statuses?

Adding the user filter would get all Task objects that are associated with the User, regardless of Status (0 or what have you).

Answer 2

Well... the only way I think you can achieve this is with LEFT OUTER JOINs or SUBQUERYs... I have no idea on how to express left outer joins in django, so I'll go with the SUBQUERY path. The following will use extra with some handcrafted SQL, enjoy!

# you should have Task and Status imported
x = Status.objects.extra(select = {
    "task__count" : "SELECT COUNT(*) FROM %(task_table)s WHERE %(task_table)s.%(task_status_id)s = %(status_table)s.%(status_pk)s AND %(task_table)s.%(user_col)s = %(user_id)d" % 
    {
        "task_table" : Task._meta.db_table,
        "task_status_id" : Task._meta.get_field_by_name("status")[0].column,
        "status_table" : Status._meta.db_table,
        "status_pk" : Status._meta.pk.column,
        "user_col" : Task._meta.get_field_by_name("user")[0].column,
        "user_id" : 1

    }
})

Note that I'm using a lot of... undocumented features (for instance: Task._meta), these might break in the future (let's hope not)... but hey, they do the job.

Answer 3

Does Status.objects.annotate(Count('task')).filter(task__user=current_user) work?

What you want is, at the SQL level, a LEFT JOIN or somesuch, with the Status table on the left, rather than an INNER JOIN. Not sure how that interacts with annotations, though.

Brute force is [stat.filter(task__user=current_user).count() for stat in Status.objects.all()] if all else fails, but that's N queries instead of one.