CODEWITHSUNDEEP
djangodjango-models
Charles Sartori
Charles Sartori

Reputation: 41

Django queryset How to retrieve related fields with annotate()

I have this table:

id  supply   event_date   value_average
----------------------------------------
1     a      01-01-2018        5
2     b      02-01-2018        6
3     a      02-01-2018        7
4     b      03-01-2018        8
5     c      03-01-2018        9

I am trying to get the latest value for each supply based on event_date column. I can get the latest event, but I did not found a way to return the value_average as well.

values_average = Purchase.objects \
    .values('supply') \
    .annotate(last=Max('event_date')) \
    .order_by()

current return:

a        02-01-2018
b        03-01-2018
c        03-01-2018

expected return:

a        02-01-2018      7
b        03-01-2018      8
c        03-01-2018      9

Upvotes: 0

Views: 63

Answers (2)

Charles Sartori
Charles Sartori

Reputation: 41

I found a way to do that by following this answer:

Django: select values with max timestamps or join to the same table

values_average = Purchase.objects \
            .filter(farm=farm, supply__in=queryset) \
            .order_by('supply', '-event_date') \
            .distinct('supply')

It will only work with Postgres. The final result will be a normal queryset with the latest events. Just take care if your model has Meta ordering.

Django docs on this: https://docs.djangoproject.com/en/dev/ref/models/querysets/#django.db.models.query.QuerySet.distinct

Upvotes: 1

Ines Tlili
Ines Tlili

Reputation: 810

I think you just have to add the value_average attribute to the values you want to return :

values_average= Purchase.objects.values('supply','value_average').annotate(last=Max('event_date'))

Upvotes: 0

Related Questions