Ordering by a field in reverse side foreign key in Django

Question

I have the following models

class Question(models.Model):
 content=models.TextField()
 ...
class Answer(models.Model):
 created_at = models.DatetimeField(auto_add=True)
 author = models.ForeignKey(User, ... )
 content = models.TextField()
 question = models.ForeignKey(Question, ....)
 ...
 class meta:
 unique_together=('author','question')

in the database I have a set of questions and answers. answers are linked to their authors. unique_together ensures the user can give an answer only once to a question.

usr_1 Is a user instance that has answers to some questions that I can retrieve by:

qst_qs=Question.objects.filter(answer__author=usr_1)

I want to sort questions in qst_qs according to the created_at field in usr_1's answer in each question.

Thanks in advance.

Answer 1

Use an annotation for usr_1’s answer timestamp, then order by it:

from django.db.models import Max
qst_qs = (Question.objects.filter(answer__author=usr_1)
     .annotate(user_answer_created_at=Max("answer__created_at"))
     .order_by("-user_answer_created_at"))

annotate() adds the per-question answer time (for that user), and order_by() sorts by it.

Answer 2

Please try this code:

qst_qs=Question.objects.filter(answer__author=usr_1).order_by('answer__created_at')