How to obtain a count of objects per auth.user?

Question

I have a Project model similar to:

class Project(models.Model):
    ...
    lead_analyst=models.ForeignKey(User, related_name='lead_analyst')
    ...

I want to use django aggregation to return all users with a count of projects per user. Something like:

models.User.objects.annotate(project_count=Count('project_set'))

Only that doesn't work because the auth.user has no knowledge about my Project class. I get the error:

Cannot resolve keyword 'project_set' into field. Choices are: date_joined, email, employee, first_name, groups, id, is_active, is_staff, is_superuser, last_login, last_name, lead_analyst, logentry, message, password, siteprofile, submitter, user_permissions, username

Two part question, really:

1. How to obtain this count of Projects per auth.user

2. Is there a better way to write the association between my Project class and the auth.user class that would make this aggregation viable?

Or should I just break into raw sql for aggregations (either via raw sql in django or a view in the database)?

Answer 1

Nothing wrong with your models - that's exactly how to set up that relation. The problem is simply that you've specified a related_name in the foreignkey, so as far as User is concerned, there's no project_set - there's a lead_analyst instead. In fact you can see this in the list of fields given by your error message.

So your aggregate query should read:

models.User.objects.annotate(project_count=Count('lead_analyst'))

(I must say, I think you've chosen a misleading related name here - you would be better off sticking to the default, in which case your original syntax would have worked.)