[Fixed]-Django query with order_by, distinct and limit on Postgresql


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)\


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.

Leave a comment