Order by nested objects date

Question

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       |

Jura Brazdil · Accepted Answer · 2021-11-09T13:47:20.470

1

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

edited Nov 09 '21 at 13:47

answered Nov 09 '21 at 13:26

Jura Brazdil

970
7
15

Hey thanks for your answer. What I need is a list of `ChatRoom`s, not the counters though. – Eugene Nov 09 '21 at 13:30
No worries. The `values_list` returns exactly what's in your expected result. I'll turn the answer around to get a Room Queryset in a sec. – Jura Brazdil Nov 09 '21 at 13:31
Thanks a lot, I'll give it a spin as soon as I can – Eugene Nov 09 '21 at 13:32
Is there a way to make this work with an actual queryset and not valuest_list by the way? I'm using django rest framework and I'd lose out on its default pagination and filter backend if I transform the queryset – Eugene Nov 09 '21 at 13:37
ah nevermind, the `rooms` is the actual queryset, I'm blind – Eugene Nov 09 '21 at 13:38
I edited the answer, so you get a queryset at the end which will act as if the room model has the `unread_count` and `latest_message` fields. So filtering, ordering and other qs operations over those 2 fields should work and every object in the qs should have them as attributes. – Jura Brazdil Nov 09 '21 at 13:38
Getting this error now `This queryset contains a reference to an outer query and may only be used in a subquery.`, trying to figure it out – Eugene Nov 09 '21 at 13:45
1

Does `.first()` evaluate the queryset? That might be the problem here since we're trying to use it in a subquery – Eugene Nov 09 '21 at 13:46
I've just updated it. You're right :) – Jura Brazdil Nov 09 '21 at 13:47
SubQuery does need to evaluate into a QS – Jura Brazdil Nov 09 '21 at 13:47
yeah I just replaced .first with [:1] and it worked, just need to test this now. The problem I'm seeing is that there is no timestamp returned for the latest message – Eugene Nov 09 '21 at 13:49
actually nevermind that, I haven't included it into the serializer yet, will test and write back – Eugene Nov 09 '21 at 13:52
For every object in the room qs, you get the `created_at` of the latest message, so that should be the timestamp :) – Jura Brazdil Nov 09 '21 at 13:52
Here's another problem. The rooms that have no messages in them are being returned first, since their `latest_message_time` is null, any way for me to make the nulls come in last? – Eugene Nov 09 '21 at 13:56
Figured it out `qs = rooms.order_by('-unread_count', F('latest_message_time').desc(nulls_last=True), 'name')` – Eugene Nov 09 '21 at 13:59

Order by nested objects date

1 Answers1