How to use the ORM for the equivalent of a SQL count, group and join query?

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?

Take a look at annotation and aggregation https://docs.djangoproject.com/el/1.10/topics/db/aggregation/ — Kritz, Oct 21 '16 at 11:13
Hey, @TomBrock I was wondering, did you found my answer helpful? — John Moutafis, Jan 09 '18 at 08:02

score 1 · Answer 1 · answered Aug 08 '17 at 08:47

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.

How to use the ORM for the equivalent of a SQL count, group and join query?

1 Answers1

Linked