Django Query That Get Most Recent Objects From Different Categories

Question

I have two models A and B. All B objects have a foreign key to an A object. Given a set of A objects, is there anyway to use the ORM to get a set of B objects containing the most recent object created for each A object.

Here's an simplified example:

class Bakery(models.Model):     town = models.CharField(max_length=255)  class Cake(models.Model):     bakery = models.ForeignKey(Bakery, on_delete=models.CASCADE)     baked_at = models.DateTimeField() 

So I'm looking for a query that returns the most recent cake baked in each bakery in Anytown, USA.

Answer 1

Starting from Django 1.11 and thanks to Subquery and OuterRef, we can finally build a latest-per-group query using the ORM.

hottest_cakes = Cake.objects.filter(     baked_at=Subquery(         (Cake.objects             .filter(bakery=OuterRef('bakery'))             .values('bakery')             .annotate(last_bake=Max('baked_at'))             .values('last_bake')[:1]         )     ) )  #BONUS, we can now use this for prefetch_related() bakeries = Bakery.objects.all().prefetch_related(     Prefetch('cake_set',         queryset=hottest_cakes,         to_attr='hottest_cakes'     ) )  #usage for bakery in bakeries:     print 'Bakery %s has %s hottest_cakes' % (bakery, len(bakery.hottest_cakes))