Django Get Last Object for each Value in List

Question

I have a model called Purchase, with two fields, User and amount_spent.

This is models.py:

class Purchase(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    amount_spent = models.IntegerField()
    created_at = models.DateTimeField(auto_now_add=True)

I want to get the last purchases from a list of users. On views.py I have a list with some User's objects, and I want to get the last purchase for each user in the list. I can't find a way of doing this in a single query, I checked the latest() operator on QuerySets, but it only returns one object.

This is views.py:

purchases = Purchase.objects.filter(user__in=list_of_users)
# purchases contains all the purchases from users, now I need to get the most recent onces for each user.

I now I could group the purchases by user and then get the most recent ones, but I was wondering it there is a way of making this as a single query to DB.

database `sqlite3` or `postgres`? and you want latest purchased in order or latest purchased one obj? — Hemal Patel, Nov 16 '22 at 12:18
I'm using PostgreSQL. I need just the lastest `Purchase` object, one per user. — Stack, Nov 16 '22 at 12:27

score 2 · Answer 1 · answered Nov 16 '22 at 12:29

2

try this:

Purchase.objects.filter(user__in=list_of_users).values("user_id", "amount_spent").order_by("-id").distinct("user_id")

answered Nov 16 '22 at 12:29

Hemal Patel

878
6
16

Willem Van Onsem · Accepted Answer · 2022-11-16T21:55:27.213

You can annotate the Users with the last_purchase_pks and then fetch these and adds that to these users:

from django.db.models import OuterRef, Subquery

users = User.objects.annotate(
    last_purchase_pk=Subquery(
        purchase.objects.order_by('-created_at')
        .filter(user_id=OuterRef('pk'))
        .values('pk')[:1]
    )
)

purchases = {
    p.pk: p
    for p in Purchase.objects.filter(
        pk__in=[user.last_purchase_pk for user in users]
    )
}
for user in users:
    user.last_purchase = purchases.get(user.last_purchase_pk)

After this code snippet, the User objects in users will all have a last_purchase attribute that contains the last Purchase for that user, or None in case there is no such purchase.

Django Get Last Object for each Value in List

2 Answers2