[Fixed]-Django equivalent for latest entry for each user

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.

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

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.

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.

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.