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)\ .order_by('-created_at')[:10]
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.