Django 1.11 order by field on related model duplicate results workaround

user_4244790 0
Viewed 729

In Django 1.11 I have a parent and child model (one to many relationship) simplified below:

class Conversation(models.Model):
  name = models.CharField(max_length=150)

class Comment(models.Model):
  comment_text = models.TextField()
  submitted_date = models.DateTimeField()
  conversation = models.ForeignKey(Conversation, on_delete=models.CASCADE)

  class Meta:
    ordering = ['-submitted_date']

A conversation can have many comments. Now what I want to do is order conversations by which one has the most recent comments. I attempted to add this to the Conversation model:

class Meta:
  ordering = ['-comment__submitted_date']

And this sort of works, but it returns duplicates in the queryset - this duplicate behavior is well documented in Django and why it happens - https://docs.djangoproject.com/en/1.11/ref/models/querysets/#order-by - but it doesn't say anything about how to work around it.

I'm looking for a way to work around this limitation. The overall goal is: have conversations sorted by most recent comment (submitted_date). I've tried multiple variations, but it either doesn't sort at all, or it returns duplicates (which isn't useful to me). distinct() won't work either, which is also documented in the link.

I could add an 'updated_at' or similar field to Conversation, and update that whenever a comment is created/updated, but that feels really hacky and unclean to me, and I'd rather avoid it if possible.

Any suggestions?

django-models django python
1 Answers

I think one way is to find the date of the last comment for each conversation and then sort by that

Conversation.objects.annotate(last_comment=Max('comment__submitted_date')) \
                    .order_by("last_comment")
user_67317650
Related