CODEWITHSUNDEEP
djangosorting
dandu
dandu

Reputation: 800

django sort query result by occurrence count

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?

Upvotes: 4

Views: 2843

Answers (2)

dandu
dandu

Reputation: 800

profiles = Profile.objects.filter(profile__ItemList=X)

Item.objects.filter(
    user_itemlist__in=profiles
).annotate(itemcount=Count('id')).order_by('-itemcount')

Upvotes: 7

Jiaaro
Jiaaro

Reputation: 76898

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'))
sorted_items = sorted_items.order_by('-itemcount')

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

Upvotes: 3

Related Questions