How to use django annotate() and aggregate() to group a queryset?

Question

I am trying to translate a sql query in django's ORM. Tried to use annotate() but I don't manage to get same results than the sql query.

This is my model :

class Fuel(models.Model):
    type = models.CharField(max_length=150)

class Station(models.Model):
    name = models.CharField(max_length=150, null=True)

class Price(models.Model):
    fuel_type = models.ForeignKey(Fuel)
    station = models.ForeignKey(Station)
    price = models.FloatField()
    date = models.DateTimeField('Date', auto_now_add=True)

And this is the query I try to translate in django :

select * from myapp_price where id IN ((select max(id) from myapp_price where station_id=121600 group by fuel_type_id));

Is this possible ?

orgoz · Accepted Answer · 2011-08-21 17:37:08Z

4

I get the expected results this way :

q=Price.objects.filter(station=filters['station_id']).values('fuel_type').annotate(Max('id')).values('id__max')
Price.objects.filter(pk__in=q)

answered Aug 21, 2011 at 17:37

orgoz

1513 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

D Dhaliwal Over a year ago

thanks a lot @orgoz, yesterday I checked your answer but I skipped .values('id__max') from first query, this solved my problem :)

Collectives™ on Stack Overflow

How to use django annotate() and aggregate() to group a queryset?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related