英文:
How to sort distinct values with respect to manytoone class in Django?
问题
我有两个模型：
class MessageThread(models.Model):
title = models.CharField(max_length=120, blank=True, null=True)
created_user = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, related_name='created_user')
belonging_user = models.ForeignKey(User, on_delete=models.SET_NULL, null=True)
last_message_date = models.DateTimeField(blank=True, null=True)
和
class Message(models.Model):
thread = models.ForeignKey(MessageThread, on_delete=models.SET_NULL, null=True)
user = models.ForeignKey(User, null=True, blank=True, on_delete=models.SET_NULL)
comments = models.TextField(blank=True, null=True, max_length=500)
create_date = models.DateTimeField(blank=True, null=True)
在这里，我想按照它们最后消息的create_date对MessageThread进行排序。
我尝试通过'-create_date'获取排序后的消息，然后从该查询中获取不同的线程ID，但它不起作用。我正在使用PostgreSQL。
英文:
I have two models
class MessageThread(models.Model):
title = models.CharField(max_length=120, blank=True, null=True)
created_user = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, related_name='created_user')
belonging_user = models.ForeignKey(User, on_delete=models.SET_NULL, null=True)
last_message_date = models.DateTimeField(blank=True, null=True)
and
class Message(models.Model):
thread = models.ForeignKey(MessageThread, on_delete=models.SET_NULL, null=True)
user = models.ForeignKey(User, null=True, blank=True, on_delete=models.SET_NULL)
comments = models.TextField(blank=True, null=True, max_length=500)
create_date = models.DateTimeField(blank=True, null=True)
Here, I want to get MessageThreads that are sorted by their last Messages' create_date.
I tried to get sorted Messages by '-create_date' and then get distinct thread ids from that query but it doesnt work. I am using PostgreSQL.
答案1
得分: 1
你可以按照message__created_date的最大值来排序，所以：
from django.db.models import F, Max
MessageThread.objects.alias(
last_message=Max('message__create_date')
).order_by(F('last_message').desc(nulls_last=True))
在MessageThread模型中，不应该有一个名为last_message_date的字段：你可以在需要时动态确定这个值。
注意：Django的DateTimeField具有auto_now_add参数来处理时间戳。这将在创建对象时自动分配当前日期时间，并将其标记为不可编辑（editable=False），因此默认情况下不会出现在ModelForm中。
注意：根据数据库后端，使用NULL进行排序会有所不同。你可以使用.desc(nulls_last=True)来确保在降序排序时NULL将被放在最后。
英文:
You can order by the maximum of the message__created_date, so:
<pre><code>from django.db.models import F, Max
MessageThread.objects.alias(
<b>last_message=Max('message__create_date')</b>
).order_by(<b>F('last_message').desc(nulls_last=True)</b>)</code></pre>
You should not have a field last_message_date in the MessageThread model: you can determine this dynamically, when needed.
> Note: Django's DateTimeField <sup>[Django-doc]</sup>
> has a <code>auto_now_add=…</code> parameter <sup>[Django-doc]</sup>
> to work with timestamps. This will automatically assign the current datetime
> when creating the object, and mark it as non-editable (editable=False), such
> that it does not appear in ModelForms by default.
> Note: Ordering with NULLs depends on the database backend. You can work with .desc(nulls_last=True) <sup>[Django-doc]</sup> to ensure that the NULLs will be put last when ordering in descending order.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论