Quick counting with linked Django Models

Question

I've got a stack of Models something like this (I'm typing it out in relative shorthand):

class User:
    pass


class ItemList:
    pass


# A User can have more than one ItemList
# An ItemList can have more than one User
# Classic M2M

class ItemListOwnership:
    user = fk(User)
    itemlist = fk(ItemList)


# An ItemList has multiple Items

class Item:
    itemlist = fk(ItemList)

What I want to do is display a simple count of how many items a given user (or existing queryset of users) have on their various lists. I need to count across the M2M divide.

That's where I'm stuck. I'm happy with a pure SQL method if that's the only thing that works but I really don't want to get into a situation where I'm issuing n + 1 queries per user (where n is the number of lists a user has) just for a count...

Edit: I'm not using a "real" models.ManyToMany(..) relationship because I define additional things in ItemListOwnership that describe the relationship slightly better. If it's a killer, I can probably move this metadata elsewhere and get rid of ItemListOwnership and stick a m2m in ItemList

Answer 1

Firstly, defining extra fields in a linking table is what the through functionality of ManyToManyField is for. So, keep your ItemListOwnership table with its FKs, but add a userlist=ManyToMany('User', through='ItemListOwnership') to UserList.

Once you've done this, you can easily count the number of items for each user by using annotate:

from django.db.models import Count
User.objects.all().annotate(item_count=Count('userlist__item'))

Now each user has an item_count attribute which is the number of items they have.