python - Django aggregate top value of foreign key -

i have django models this

class solution(models.model):     score = models.integerfield();     data = models.textfield();  class solutionauthors(models.model):     sol = models.foreignkey(solution);     user = models.foreignkey(user);     date_solved = models.datefield() 

and want query recent solutionauthor each solution.

the equivalent sql might

select * solutions s inner join solutionauthor sa on sa.sol=s.sol sa.id = ( select top 1 sa_id solutionauthor sa2 sa2.sol=s.sol                 order date_solved ) 

any thoughts on how annotate feature or otherwise? solutions reorganize schema welcome well.

you have > 8k rep, means that:

• you know it not possible write filters on annotation.
• you able write solution model method latest solution.

here sample solution model method, annotate in query not needed because can call method @ time:

class solution(models.model):     score = models.integerfield()     data = models.textfield()           @method     def last_solution(self):         return self.objects.solutionauthors_set.latest('date_solved') 

if not approach may because performance issues or because can not include filters through model method in query. if app solution persist last solution author solution model:

class solution(models.model):     score = models.integerfield()     data = models.textfield()           #new persisted field:     last_solution = models.foreignkey('solutionauthors', null=true, blanc=true);  #keeping last_solution date: #imports handle signals: django.db.models.signals import post_save django.dispatch import receiver  #persisting data @receiver(post_save, sender=solutionauthors) def set_last_solution(sender, instance, signal, created, **kwargs):     if created or instance.date_solved > instance.sol.last_solution.date_solved:         instance.sol.last_solution = instance         instance.sol.save() 

this approach improve performance query through last solution. disadvantage denormalization of database, think must changing in attitude towards subject.