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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With