How to use order_by with last_added manytomanyfield?

Question

Models.py

class ChatRoomId(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    created_date = models.DateTimeField(auto_now_add=True)
    

class MessageContent(models.Model):
    content = models.TextField(blank=True)
    created_date = models.DateTimeField(auto_now_add=True)
    modified_date = models.DateTimeField(auto_now=True)
    

class PersonalMessage(models.Model):
    message = models.ForeignKey(MessageContent, on_delete=models.CASCADE)
    sender = models.ForeignKey(CustomUser, on_delete=models.DO_NOTHING)


class PersonalRoom(models.Model):
    room_id = models.ForeignKey(ChatRoomId, on_delete=models.CASCADE)
    user = models.ManyToManyField(CustomUser)
    message_list = models.ManyToManyField(PersonalMessage)
    modified_date = models.DateTimeField(auto_now=True)

How to order this queryset PersonalRoom.objects.all() by the value "message__created_date" ManyToManyField last added message_list object, which is last_item = message_list.all().order_by('-message__created_date')[0].

My idea is

PersonalRoom.objects.all().order_by(
    Subquery(
        PersonalRoom.objects.get(pk=OuterRef('pk')).message_list.all().order_by('-message__created_date')[0]
    )
)

but this results in an error.

Please remember that Stack Overflow is not your favourite Python forum, but rather a question and answer site for all programming related questions. Thus, please always include the tag of the language you are programming in, that way other users familiar with that language can more easily find your question. Take the [tour] and read up on [ask] to get more information on how this site works. — Adriaan, Aug 19 '22 at 07:34
Can you share the error that appears when you try your solution? — enes islam, Aug 19 '22 at 07:37
ValueError at /app/ This queryset contains a reference to an outer query and may only be used in a subquery. @enesislam — Shankar Balaji U, Aug 19 '22 at 07:45

score 0 · Answer 1 · answered Aug 19 '22 at 08:52

0

Try reverse relationship like:

PersonalRoom.objects.all().order_by('-message_list__message__created_date')

answered Aug 19 '22 at 08:52

Waldemar Podsiadło

1,365
2
5
12

I do this way, but i got duplicate PersonalRoom objects. So i tried distinct('room_id') and it's throw some error – Shankar Balaji U Aug 19 '22 at 14:35
I'm not try to get this result. What im trying is, sort the PersonalRoom objects by the created_date of the last added object in messsage_list – Shankar Balaji U Aug 19 '22 at 14:44

score 0 · Answer 2 · answered Aug 19 '22 at 17:06

models.py

class PersonalRoom(models.Model):
    room_id = models.ForeignKey(ChatRoomId, on_delete=models.CASCADE)
    user = models.ManyToManyField(CustomUser)
    message_list = models.ManyToManyField(PersonalMessage)
    modified_date = models.DateTimeField(auto_now=True)

    @property
    def last_message_date(self):
        _message = self.message_list.all().order_by('-message__created_date')[0]
        _date = _message.message.created_date
        return _date

views.py

_rooms = PersonalRoom.objects.filter(user__in=[request.user], message_list__isnull=False).distinct()
sorted_rooms = sorted(_rooms, key=lambda item: item.last_message_date, reverse=True)

sorted_rooms is the reverse sorted PersonalRoom, where all the user related room models are sorted by the last added message with created date.

How to use order_by with last_added manytomanyfield?

2 Answers2