Django query with order_by, distinct and limit on Postgresql

Question

I have the following :

class Product(models.Model):
    name = models.CharField(max_length=255)

class Action(models.Model):
    product = models.ForeignKey(Product)
    created_at = models.DateTimeField(auto_now_add=True)

I would like to retrieve the 10 most recent actions ordered by created_at DESC with distinct products.

The following is close to the result but still misses the ordering:

Action.objects.all().order_by('product_id').distinct('product_id')[:10]

Answer 1

Your solution seems like it's trying to do too much. It will also result in 2 separate SQL queries. This would work fine and with only a single query:

action_ids = Action.objects.order_by('product_id', '-created_at')\
    .distinct('product_id').values_list('id', flat=True)

result = Action.objects.filter(id__in=action_ids)\
    .order_by('-created_at')[:10]

Answer 2

EDIT: this solution works but Ross Lote's is cleaner

This is the way I finally did it, using Django Aggregation:

from django.db.models import Max
    
actions_id = Action.objects.all().values('product_id') \
    .annotate(action_id=Max('id')) \
    .order_by('-action_id')[:10] \
    .values_list('action_id', flat=True)

result = Action.objects.filter(id__in=actions_id).order_by('-created_at')

By setting values('product_id') we do a group by on product_id.

With annotate() we can use order_by only on fields used in values() or annotate(). Since for each action the created_at field is automatically set to now, ordering on created_at is the same as ordering on id, using annotate(action_id=Max('id')).order_by('-action_id') is the right way.

Finnaly, we just need to slice our query [:10]

Hope this helps.