Django Query using .order_by() and .latest()

Question

I have a model:

class MyModel(models.Model):
   creation_date = models.DateTimeField(auto_now_add = True, editable=False)

   class Meta:
      get_latest_by = 'creation_date'

I had a query in my view that did the following:

instances = MyModel.objects.all().order_by('creation_date')

And then later I wanted instances.latest(), but it would not give me the correct instance, in fact it gave me the first instance. Only when I set order_by to -creation_date or actually removed the order_by from the query did .latest() give me the correct instance. This also happens when I test this manually using python manage.py shell instead of in the view.

So what I've done now is in the Model's Meta I've listed order_by = ['creation_date'] and not used that in the query, and that works.

I would have expected .latest() to always return the most recent instance based on a (date)(time) field. Could anyone tell me whether it's correct that .latest() behaves strangely when you use order_by in the query?

Answer 1

I would have expected .latest() to always return the most recent instance based on a (date)(time) field.

The documentation says that

If your model's Meta specifies get_latest_by, you can leave off the field_name argument to latest(). Django will use the field specified in get_latest_by by default.

All this means is that when you fire MyModel.objects.latest() you will get the latest instance based on the date/time field. And when I tested your code using sample data, it indeed did.

And then later I wanted instances.latest(), but it would not give me the correct instance, in fact it gave me the first instance.

You have misunderstood the way latest() works. When called on MyModel.objects it returns the latest instance in the table. When called on a queryset, latest will return the first object in the queryset. Your queryset consisted of all instances of MyModel ordered by creation_date in ascending order. It is only natural then that latest on this queryset should return the first row of the queryset. This incidentally happens to be the oldest row in the table.

One way to get a better understanding is to view the query fired for latest.

Case 1:

from django.db import connection
MyModel.objects.latest()
print connection.queries[-1]['sql']

This prints:

SELECT "app_mymodel"."id", "app_mymodel"."creation_date" FROM 
"app_mymodel" ORDER BY "app_mymodel"."creation_date" DESC LIMIT 1

Note the ordering by creation_date DESC and the LIMIT clause. The former is thanks to get_latest_by whereas the latter is the contribution of latest.

Now, case 2:

MyModel.objects.order_by('creation_date').latest()
print connection.queries[-1]['sql']

prints

SELECT "app_mymodel"."id", "app_mymodel"."creation_date" FROM 
"app_mymodel" ORDER BY "app_mymodel"."creation_date" ASC LIMIT 1

Note that the ordering has changed to creation_date ASC. This is the result of the explicit order_by. The LIMIT is tacked on er, later courtesy latest.

Let us also see Case 3: where you explicitly specify the field_name for objects.latest().

MyModel.objects.latest('id')
print connection.queries[-1]['sql']

shows

SELECT "app_mymodel"."id", "app_mymodel"."creation_date" FROM "app_mymodel"
ORDER BY "app_mymodel"."id" DESC LIMIT 1