2

Imagine we have a table like this:

id name type created_at
1 James male 2022-03-02
2 Jane female 2022-04-02
3 Kirk male 2022-03-04
4 Sarah female 2022-04-04
5 Jason male 2022-03-05

And i want to group by type and just get latest records based on created_at.
So i tried this code and not bad:

result = User.objects.values('type').annotate(
            latest_date=Max('created_at'),
)

When print the result i face to this:

<QuerySet [
{'type': 'male', 'latest_date': '2022-03-05'}, 
{'type': 'female', 'latest_date': '2022-04-04'}
]>

My question is: Where is other fields id and name? I expect to get:

<QuerySet [
{id: 5, name: 'Jason', 'type': 'male', 'latest_date': '2022-03-05'}, 
{id: 4, name: 'Sarah', 'type': 'female', 'latest_date': '2022-04-04'}
]>
reza_khalafi
  • 6,230
  • 7
  • 56
  • 82

2 Answers2

2

Use order_by with distinct

User.objects.order_by("type", "-created_at").distinct("type")
mehrh8
  • 329
  • 2
  • 5
0

this might help you

result = User.objects.values('type').annotate(
                latest_date=Max('created_at'),
    ).values('id','name','type','latest_date')
lord stock
  • 1,191
  • 5
  • 32