Django: order_by column from another table-CodePudding

I am building a forum and have the next model for messages:

class Message(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    text = models.TextField(max_length=100000)
    date_publication = models.DateTimeField(auto_now_add=True)


class Discussion(Message):
    title = models.CharField(max_length=500)
    views = models.PositiveBigIntegerField(default=0)


class Response(Message):
    topic = models.ForeignKey(Discussion, on_delete=models.CASCADE)
    reply_to = models.ForeignKey(
        Message, on_delete=models.CASCADE, related_name='message_replied', null=True)

I wonder how can I get a list of Discussions order_by date_publication from Response.

CodePudding user response：

You can order with:

from django.db.models import Max
from django.db.models.functions import Coalesce

Discussion.objects.alias(
    latest_reply=Coalesce(Max('response__reply_to__date_publication'), 'date_publication')
).order_by('-latest_reply')

or for django-3.1 and earlier:

from django.db.models import Max
from django.db.models.functions import Coalesce

Discussion.objects.annotate(
    latest_reply=Coalesce(Max('response__reply_to__date_publication'), 'date_publication')
).order_by('-latest_reply')

CodePudding user response：

I fixed it this way:

from django.db.models import Max
from django.db.models.functions import Coalesce

Discussion.objects.alias(
    latest_reply=Coalesce(
        Max('response__date_publication'), 'date_publication')
).order_by('-latest_reply')