Django queryset How to retrieve related fields with annotate()

Question

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

Charles Sartori · Accepted Answer · 2018-08-08 16:52:40Z

1

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

answered Aug 8, 2018 at 16:52

Charles Sartori

414 bronze badges

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

Comments

Ines Tlili · Accepted Answer · 2018-08-08 15:32:53Z

0

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

answered Aug 8, 2018 at 15:32

Ines Tlili

8108 silver badges21 bronze badges

3 Comments

Charles Sartori Over a year ago

If I add value_average the result query will group by value_average too, and give me the wrong result. with value_average: ...GROUP BY "event_event"."supply_id", "event_event"."value_average" without value_average: ...GROUP BY "event_event"."supply_id"

Ines Tlili Over a year ago

Im not sure I get you but did you try to group by supply ? like this : values_average= Purchase.objects.values('supply','value_average').annotate(last=Max('event_date')).groupby('suply')

Charles Sartori Over a year ago

As far as I know, .groupby() does not exists in the ORM. To group by something we must use values(). docs.djangoproject.com/en/2.1/topics/db/aggregation/#values Please, If I am missing something, could you point me out the docs related to it?

Collectives™ on Stack Overflow

Django queryset How to retrieve related fields with annotate()

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related