Annotate with value of latest related in Django 1.8 using conditional annotation

Question

I have the following models:

class City(models.Model):     ...  class Census(models.Model):     city = models.ForeignKey(City)     date = models.DateTimeField()     value = models.BigIntegerField() 

Now I'd like to annotate a City-queryset with the value of the latest Census. How do I achieve that?

I have tried:

City.objects.annotate(population=Max('census__date')) # --> annotates date and not value  City.objects.annotate(population=Max('census__value')) # --> annotates highest value, not latest  City.objects.annotate(population=     Case(         When(             census__date=Max('census__date'),             then='census__value')         )     )  # --> annotates 'None'  City.objects.annotate(population=     Case(         When(             census__date=Max('census__date'),             then='census__value')         ), output_field=BigIntegerField()     )  # --> takes forever (not sure what happens at the end, after some minutes I stopped waiting) 

Any help greatly appreciated!

Answer 1

I've also been having an issue where I need a max value object of a related set, but I require the entire object. I have not been able to figure out a solution using annotation and Case. Until I do, I use this prefetching solution. If each city does not have a large amount of census objects, or if your application is not performance bound, this may work for you.

inner_qs = Census.objects.order_by('-date') cities = City.objects.prefetch_related(Prefetch("census_set", queryset=inner_qs, to_attr="census_list"))  class City(models.Model):     @property     def latest_census(self):         if hasattr(self, 'census_list') and len(self.census_list) > 0:             return self.census_list[0]         return None 

If this does not work for you, consider some of the suggestions found here: http://blog.roseman.org.uk/2010/08/14/getting-related-item-aggregate/