How to use the ORM for the equivalent of a SQL cou

Question

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.

Here are the models:

LOCATIONS = (
    ('US', 'USA'),
    ('UK', 'United Kingdom'),
    ('FR', 'France'),
)

class Location(models.Model):
    location = models.CharField(choices=LOCATIONS)

class Image(models.Model):
    image = models.ImageField(verbose_name='Image')

class Tag(models.Model):
    tag = models.CharField(max_length=150, unique=True)

class Tag_Item(models.Model):
    tag = models.ForeignKey(Tag, on_delete=models.CASCADE)    
    location = models.ForeignKey(Location, null=True, blank=True, default=None)
    image = models.ForeignKey(Image, null=True, blank=True, default=None)
    created_at = models.DateTimeField(auto_now_add=True)

I want to write a query which means select the five most frequent tags for USA.

I was thinking something along the following lines in SQL:

• Join Tag, Tag_Item, and Location where location is 'US'.
• Group it by tag.
• Order it by a count of Tag_ID (or something along those lines).

but I can't figure out how to transfer that in Django ORM.

Can you please help me on how to write that sort of complex relationship queries?

Answer 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.