Get latest entry in django in multiple dimensions

Question

I have a Django model that looks something like this:

class MyModel(Model):
     field_1 = IntegerField()
     field_2 = IntegerField()
     field_3 = IntegerField()
     value = IntegerField()
     time_stamp = DateTimeField()

I want to find the latest entry for all sets of combinations of (field1, field2, field3) tuples, and grab the value of value from that row.
For example lets say the table has the following rows:

Field1, Field2, Field3, TimeStamp, Value
1     , 1     , 1     , 1/1/2015 , 1
1     , 1     , 1     , 1/2/2015 , 2
1     , 1     , 2     , 1/1/2015 , 3
2     , 2     , 2     , 1/1/2015 , 4

I would want the result of my query to return rows 2,3,4

I could obviously do something like:

dims = MyModel.objects.values('field1', 'field2', 'field3').distinct()
for dim in dims:
    row = MyModel.objects.filter(**dim).latest('time_stamp')
    value = row.value

But that seems like a lot of DB hits, there a way to do this with fewer touches?

Answer 1

This should do it in one go at the database (tested with PostGreSQL):

MyModel.objects.order_by('field_1__pk', 'field_2__pk', 'field_3__pk', '-time_stamp')
.distinct('field_1__pk', 'field_2__pk', 'field_3__pk')
.values('value')

The order_by groups identical field_x-triplets together with the latest time_stamp first, distinct then selects only the first row for each such triplet. This works for your simple model, but cautiousness is generally required when combining order_by, distinct and values: https://docs.djangoproject.com/en/1.9/ref/models/querysets/#django.db.models.query.QuerySet.distinct

EDIT: Updated the answer to use the pk's of the fields. When they are not ordinary types the order_by does not work super properly. However using the pk's gives the proper result.