[Answered ]-Order by nested objects date

by

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

👤Jura Brazdil

Leave a comment