Getting value of field of a reverse related model in django queryset

Question

class User(models.Model):
    name = models.CharField(max_length=189)

class Chat(models.Model):
    message = models.TextField()
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name="messages")
    created_at = models.DateTimeField(auto_now_add=True)

What I want to do here is that I want to get a queryset of users ordered by according their last message time which will also contain the last message of the user as an additional data.

I have tried the following.

qs = User.objects.annotate(
        last_message_time=Max("messages__created_at"),
        last_message=F("messages__message")
     ).order_by("-last_message_time")

Here, I am getting the last message time but the first message of the user.

Answer 1

I think I've found the answer.

qs = User.objects.annotate(
        last_message_time=Max("messages__created_at"),
        last_message_message=Subquery(
            Chat.objects.filter(user=OuterRef("id")).order_by("-created_at").values("message")[:1]
        )
     ).order_by("-last_message_time")

I got help from this article on this issue! https://medium.com/@hansonkd/the-dramatic-benefits-of-django-subqueries-and-annotations-4195e0dafb16

Answer 2

I don't think you can do it easily with one query. This looks like exactly what you are looking for.

You can either write a custom raw SQL query to get what you want or do something not efficient like that in two steps:

qs = User.objects.annotate(
       last_message_time=Max("messages__created_at"),
    ).order_by("-last_message_time")

for user in qs:
    user.last_message = Chat.objects.get(user=user, created_at=user.last_message_time)