Django ORM: Group by and Max

Question

I have a model that looks like this:

Requests: user, req_time, req_text 

In the DB, the records can look like this:

id, user_id, req_time, req_text 1     1      TIMESTAMP  YES 2     1      TIMESTAMP  NO 3     2      TIMESTAMP  YES 

etc.

How do I write a Django ORM query that: groups the Requests by user, filters the Requests based on req_text, and also, select the max id of the resulting result set. So for each user, I will return one row which matches the filter condition and also has the greatest id.

Answer 1

from django.db.models.aggregates import Max  request_values = Requests.objects.filter(req_text='YES') \                  .values('user') \                  .annotate(max_id=Max('id')) 

Then request_values will look like this:

[     {'user': 1, 'max_id': 1},     {'user': 2, 'max_id': 4},     {'user': 3, 'max_id': 5},     {'user': 4, 'max_id': 12},     ... ]