Django: order_by column from another table

Question

我正在建立一个论坛，并有下一个消息模型：

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)

我想知道如何从"响应"中获取"讨论"order_by date_publication的列表。

Answer 1

您可以使用订购

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')

或适用于django-3.1及更早版本：

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')

Answer 2

我是这样修复的：

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')