Fetch relations from ManyToMany Field using Annotation-CodePudding

I have my database here. Where I have 2 users connected to one instance of ChatRoomParticipants with a ManyToManyField.

I'm trying to get list of related users from a ManyToMany Relation Field from ChatRoomParticipants where I don't want to show the currently authenticated user in the list with other fields i.e room present in the model.

Considering user f4253fbd90d1471fb54180813b51d610 is currently logged in and is related to all ChatRooms via ChatRoomParticipants model.

Things I've tried but couldn't get the desired output

chatrooms = list(ChatRoomParticipants.objects.filter(user=user).values_list('user__chatroom_users__user__username', 'room').distinct())

#####
chatrooms =  ChatRoomParticipants.objects.filter(user=user).annotate(user!=User(user))

####
chatrooms = Chatrooms.objects.filter(user=user)
rooms = chatrooms.values('user__chatroom_users__user__username').distinct().exclude(user__chatroom_users__user__username=user)

I want an output like

[
    {
        'user': '872952bb6c344e50b6fd7053dfa583de'
        'room': 1
    },
    {
        'user': '99ea24b12b8c400689702b4f25ea0f40'
        'room': 2
    },
    {
        'user': 'eecd66e748744b96bde07dd37d0b83b3'
        'room': 3
    },
]

models.py

class ChatRoom(models.Model):
    name = models.CharField(max_length=256)
    last_message = models.CharField(max_length=1024, null=True)
    last_sent_user = models.ForeignKey(
        User, on_delete=models.PROTECT, null=True)

    def __str__(self):
        return self.name


class Messages(models.Model):
    room = models.ForeignKey(ChatRoom, on_delete=models.PROTECT)
    user = models.ForeignKey(User, on_delete=models.PROTECT)
    content = models.CharField(max_length=1024)
    created_at = models.DateTimeField(auto_now_add=True)

    def __str__(self):
        return self.content


class ChatRoomParticipants(models.Model):
    user = models.ManyToManyField(User, related_name='chatroom_users')
    room = models.ForeignKey(ChatRoom, on_delete=models.PROTECT)

CodePudding user response：

if you still want to keep your DB design then check the bellow link for query reference

[https://docs.djangoproject.com/en/4.0/topics/db/queries/#lookups-that-span-relationships]

CodePudding user response：

I think the database design is wrong. Especially, the user field in the ChatRoomParticipants model is defined as ManyToManyField but I think it should be just as ForeignKey because there needs to be the M2M relationship between User and ChatRoom, not between User and ChatRoomParticipants.

class ChatRoomParticipants(models.Model):
    user = models.ForeignKey(User, related_name='chatroom_users', on_delete=models.PROTECT)
    room = models.ForeignKey(ChatRoom, on_delete=models.PROTECT)

Then the filter function should work.

First you need to get the list of rooms:

room_ids = list(ChatRoomParticipants.objects.filter(user=user).values_list('room__id', flat=True))

And you get the roommates:

participants = ChatRoomParticipants.objects.exclude(user__id = user.id).filter(room__id__in = room_ids)