Best way to filter queryset by counting number of ForeignKey in Django

Question

I have 2 models, ChatRoom and Message:

Model ChatRoom:

class ChatRoom(models.Model):
    user = models.ForeignKey(User, on_delete=models.SET_NULL, related_name='chat_creator', null=True, blank=True)
    with_user = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='room_user_with')

Model Message:

class Message(models.Model):
    chat = models.ForeignKey(ChatRoom, on_delete=models.CASCADE, related_name='message_room')
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name='message_user')
    content = models.TextField()

I want to filter ChatRoom queryset by getting ChatRoom which have more than 10 messages in it.

What is the best way to do this? Thank in advance!

score 2 · Accepted Answer · answered Mar 15 '19 at 09:52

2

ChatRoom.objects.annotate(cnt=Count('message_room')).filter(cnt__gt=20)

Your related_names are misleading :); they should refer to the model containing the foreign key from the perspective of the model the foreign key refers to, e.g. chat_messages and user_messages.

answered Mar 15 '19 at 09:52

Endre Both

5,540
1
26
31

Here's [some more reading](https://stackoverflow.com/questions/2642613/what-is-related-name-used-for-in-django) on `related_name`. – Endre Both Mar 15 '19 at 09:59

Best way to filter queryset by counting number of ForeignKey in Django

1 Answers1