How To Use The Orm For The Equivalent Of A Sql Count, Group And Join Query?

I have tags which can be associated with images and locations. Tags are unique. Images and locations can have many tags. A Tag_Item model is used to link everything together. Her

Solution 1:

You will need a few things to begin a query like this:

• annotate(), will be used to perform and add the count field.
• order_by(), will be used to order the queryset.
• values(), will be used to retrieve a specific column of the table.
• Notes on GROUP BY ... COUNT: How to execute a GROUP BY ... COUNT or SUM in Django ORM?
• You can get the related field of a foreign key with the __ notation.

---

You can simplify your described query:

from django.db.models import Count

usa_tags = Tag_Item.objects.filter(location__location='US')
                           .values('tag__tag__id')
                           .annotate(my_count=Count('tag__tag__id')
                           .order_by('my_count')

That will return an ordered dictionary who's 1st entry is the top tag 2nd entry is the second tag in use etc. :

tag_id | my_count
-------|---------
   5   |   101
   1   |    53
  ...  |   ...

Now you can retrieve the five top tags from that dictionary.