CODEWITHSUNDEEP
pythondjango
user6100520
user6100520

Reputation:

Django: Order query by AVG() field in related table

I have two models

class Item(VoteModel, models.Model):
    **some_fields**

class ItemRating(models.Model):
    score = models.IntegerField(default=0)
    item_id = models.ForeignKey(Item)

I want to order Item query by Averadge score from ItemRating

In SQL, it will something like this 

SELECT it.*, AVG(ir.score) as rating FROM Item it, ItemRating ir WHERE it.id = ir.item_id ORDER BY rating;

How to do it in django ? Thank You!

Upvotes: 1

Views: 261

Answers (2)

user6100520
user6100520

Reputation:

Turned out it's very simple.

You can annotate you model's query with field from related table, and order or do other operation with it.

Code to my question:

item = Item.objects.all()               
itemsrc = item.annotate(rating=Avg('itemrating__score')).order_by('rating')

Check documentation for more details.
https://docs.djangoproject.com/en/2.0/topics/db/aggregation/#joins-and-aggregates

Upvotes: 1

Vikas Periyadath
Vikas Periyadath

Reputation: 3186

just try this one and check does it meet your requirements :

from django.db.models import Avg
frst = Item.objects.value_list('id', flat=True)  
scnd = ItemRating.objects.filter(
      item_id__in=frst).annotate(rating=Avg('score')).order_by('rating').values_list('rating', flat=True)

Upvotes: 0

Related Questions