Order by nested objects date-CodePudding

I am building a chat application and have the following models representing the chat room, chat message and the counter of unread messages

class ChatRoom(BaseModel):
    name = models.CharField(max_length=255, default='', blank=True)
    participants = models.ManyToManyField('accounts.User', related_name='chat_rooms')

class ChatMessage(BaseModel):
    text = models.TextField(default='', blank=True)
    owner = models.ForeignKey('accounts.User', on_delete=models.CASCADE)
    room = models.ForeignKey(ChatRoom, on_delete=models.CASCADE, related_name='messages')

class ChatRoomUnreadMessagesCounter(BaseModel):
    room = models.ForeignKey(ChatRoom, on_delete=models.CASCADE, related_name='unread_counters')
    owner = models.ForeignKey('accounts.User', on_delete=models.CASCADE)
    messages_count = models.IntegerField(default=0)

class BaseModel(models.Model):
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    is_deleted = models.BooleanField(default=False)

    class Meta:
        abstract = True

I would like to make an API view that would return a list of ChatRoom objects that are ordered in the following manner: ChatRoom objects that have unread messages ordered by messages count -> ChatRoom objects that have messages in them by the latest message created_at last in first out -> ChatRoom objects that are sorted alphabetically. How would I construct my queryset properly?

Here's an expected result output

| Room Name | Unread Messages | Last Message          |
| Rean      | 2               | 09.11.2021T12:00:00   |
| Ash       | 1               | 09.11.2021T12:00:24   |
| Altina    | 0               | 09.11.2021T12:15:00   |
| Juna      | 0               | 09.11.2021T12:14:00   |
| Kurt      | 0               | 09.11.2021T12:13:00   |
| Musse     | 0               | 09.11.2021T12:12:00   |
| Sharon    | 0               | No messages yet       |

CodePudding user response：

This is not really trivial. Hope someone gets a simpler answer. Mine includes SubQuery.

    from django.db.models import OuterRef, Subquery

    # get query which references an 'id' field of the parent qs
    latest_message_subq = Subquery(Message.objects.filter(
        room=OuterRef("id")).order_by("-created_at").values('created_at')[:1]
    )

    # annotate the unread count per room
    # assumes there's only a single counter per owner
    unread_count_subq = Subquery(ChatRoomUnreadMessagesCounter.objects.filter(
        room=OuterRef("id"), owner=user).values('messages_count')[:1]
    )

    # Room.objects.all() is the parent qs
    # So the OuterRef("id") is pointing to a room id
    rooms = Room.objects.annotate(
        latest_message_time=latest_message_subq,
        unread_count= unread_count_subq
    )
    # every object in the rooms qs should now have the attributes latest_message_time and unread_count; you can loop it to verify

    rooms = rooms.order_by('-unread_count', '-latest_message', 'name')

EDIT: Changed to get a Room qs at the end

CodePudding user response：

You must add the logic in the __lt__ dunder lt method in order to tell python how to sort the instances.

Here is the official documentation

This thread can be useful.