How to use ORM for equivalent SQL query, group and join?

Question

I have tags that can be associated with images and locations.

• Tags are unique.
• Images and locations can have many tags.
• The Tag_Item model is used to connect 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 that means choosing the five most commonly used tags for the USA .

I was thinking about something in the following lines in SQL:

• Attach the tag, Tag_Item and the location where "US" is located.
• Group by tag.
• Order it on account Tag_ID (or something like that).

but I can't figure out how to pass this to Django ORM.

Could you help me write such complex relationship queries?

Answer 1

, :

• annotate(), count.
• order_by(), .
• values(), .
• GROUP BY ... COUNT: GROUP BY... COUNT SUM Django ORM?
• __ .

---

:

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')

, . - ..:

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

.