Django request with order_by, excellent and limited for 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 get the last 10 actions sorted using create_at DESC with individual products.

The corollary is close to the result, but still out of order:

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

Answer 1

Your decision is like trying to do too much. This will also result in 2 separate SQL queries. This will work fine and with only one request:

 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 is cleaner

Here's how I did it using Jango 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 make the group on product_id .

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

Finnaly, we just need to chop our request [:10]

Hope this helps.