django sort query result by occurrence count

Question

I've got the flowing two models:

class Item(models.Model):
    Name = models.CharField(max_length = 32)

class Profile(models.Model):
    user = models.ForeignKey(User, unique = True)

    ItemList = models.ManyToManyField(Item, related_name = "user_itemlist")

For Item X I want to get a list of Item objects present in ItemList for all Profile objects that contain X in ItemList, sorted by how many times each object appears.

The best I can do so far is:

Item.objects.filter(user_itemlist__in = User.objects.filter(profile__ItemList = X))

and this returns the list of all Item objects I need, with duplicates (if Item Z is present in ItemList for 10 Profile objects it will appear 10 times in the query result).

How can I sort the result of the above query by the number of times each object appears in the result and remove duplicates? Is there any "django" way to do that?

Answer 1

if you're using django 1.0+ you can do this:

from django.db.models import Count
# note that 'profile' is an instance of Profile, not the model itself
sorted_items = profile.ItemList.annotate(itemcount=Count('name')).order_by('-itemcount')

#number of occurences of top item
sorted_items[0].itemcount

Answer 2

Item.objects.filter(user_itemlist__in = Profile.objects.filter(profile__ItemList = X)).annotate(itemcount=Count('id')).order_by('-itemcount')