CODEWITHSUNDEEP
djangodjango-querysetdjango-ormdjango-filter
Paul
Paul

Reputation: 167

How to query Django for the latest entry for each user?

Using Entry.objects.latest('created_at') I can recover the latest Entry for all Entry objects. But how do I get the latest Entry for each user? This is something similar to an SQL latest-record query. How do I achieve this using the ORM? Here's my approach, and 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 a subquery?

Upvotes: 15

Views: 5050

Answers (6)

CPMaurya
CPMaurya

Reputation: 391

We use annotate() and Max() This method uses annotate() to get the latest created_at for each user:

Python/Django

from django.db.models import Max

latest_entries = Entry.objects.annotate(
    latest_entry_date=Max('user__entry__created_at')
).filter(created_at=models.F('latest_entry_date'))

Upvotes: 0

Jose Boveda
Jose Boveda

Reputation: 190

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 %}

Upvotes: 3

Omid Raha
Omid Raha

Reputation: 10680

Using order_by and distinct:

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

For better performance, add an index together on the 'user' and 'created_at' fields.

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

Upvotes: 11

culebrón
culebrón

Reputation: 36463

I had a similar problem and did it this way:

priorities = obj.books_set.values('category').annotate(priority=Max('priority'))

Note: I annotate max priority as priority, because I'll reuse the output as filter condition.

It's a list of categories with min priorities. Then I do this:

>>> priorities[0]
{'category': 1, 'priority': 10}

I want to find books that have category & priority pair among one in the list. In the end the queryset condition should look like this:

Q(priorities[0]) | Q(priorities[1]) | ...

To do this in one line, use reduce on Q.__or__:

reduce(Q.__or__, (Q(**x) for x in priorities))

I know it's a bit worse than raw SQL, but safer. Comment this code if you use it, because it's hard to read.

Upvotes: 0

Jordan Reiter
Jordan Reiter

Reputation: 21002

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.

Upvotes: 1

Anatoly Rr
Anatoly Rr

Reputation: 1206

I cannot think out a single raw sql query which will fetch the set you need, so I doubt it's possible to construct a QuerySet with these results.

Upvotes: -1

Related Questions