Django equivalent for latest entry for each user

Question

I'm surprised this question hasn't come up. Couldn't find much on the web.

Using Entry.objects.latest('created_at') I can recover the latest entry for all Entry objects, but say if I want the latest entry for each user? This is something similar to an SQL latest record query. But how do I achieve this using the ORM? Here is my approach I'm wondering if it is the most efficient way to do what I want.

First I perform a sub query: Objects are grouped by user and the Max (latest) created_by field is returned for each user (created_at__max) I then filter Entry objects based on the results in the subquery and get the required objects.

Entry.objects.filter(created_at__in=Entry.objects.values('user').annotate(Max('created_at')).values_list('created_at__max'))

or using a manager:

class UsersLatest(models.Manager):  

    def get_query_set(self):
        return super(UsersLatest,self).get_query_set().filter(created_at__in=self.model.objects.values('user').annotate(Max('created_at')).values_list('created_at__max'))

Is there a more efficient way? possibly without sub query?

Thanks,

Paul

Answer 1

Using order_by and distinct:

Entry.objects.all().order_by('user', 'created_at').distinct('user')

Then for performance adding index together on 'user' and 'created_at' fields.

But i think real production way is to use Redis to cache and update an id's list of latest entries of users.

Answer 2

The design of your QuerySet depends on what you plan to use it for. I'm not sure why you're breaking out of the QuerySet iterator with the values_list method at the end. I imagine you have a status list of users where you show the last activity time based on that Entries model. For that you may want to try this:

Users.objects.all().annotate(latest_activity=Max('entries__created_at'))

And then loop through your users easily in your template with

{% for user in users %}
{{ user.full_name }}
{{ user.latest_activity|date: "m/d/Y" }}
{% endfor %}

Answer 3

The raw SQL would be

SELECT entry.id, entry.title, entry.content, entry.user_id, entry.created_at
FROM
    entry
WHERE
    entry.created_at = ( SELECT Max(e2.created_at) from entry as e2 where e2.user_id = entry.user_id )

So one option is using the where argument of the extra() modifier:

Entry.objects.extra(where='entry.created_at = ( SELECT Max(e2.created_at) from entry as e2 where e2.user_id = entry.user_id )')

Of course, you'd probably have to change entry to whatever the actual name of the table is in the database. Assuming you're comfortable looking at ._meta, you can try this:

Entry.objects.extra( where=
    '%(table)s.created_at = ( SELECT Max(e2.created_at) from %(table)s as e2 where e2.user_id = %(table)s.user_id )' % { 'table':Entry._meta.db_table }
)

There's probably a more elegant way to get the name of a table.