CODEWITHSUNDEEP
pythondjangodjango-orm
Tom Brock
Tom Brock

Reputation: 950

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.

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:

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?

Upvotes: 1

Views: 1117

Answers (1)

John Moutafis
John Moutafis

Reputation: 23134

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

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.

Upvotes: 1

Related Questions