Optimizing Django queries - reducing database requests & proper queryset access

Question

My Views

def home(request):
    q = ''
    if request.GET.get('q'):
        q = request.GET.get('q')
    rooms = Room.objects.select_related('host', 'topic').prefetch_related('participants').filter(
        Q(topic__name__icontains=q) |
        Q(name__icontains=q) |
        Q(description__icontains=q)
    )
    topics = Topic.objects.select_related('user')
    profile = Profile.objects.filter(user=request.user)
    r_messages = Message.objects.select_related('user', 'room').filter(Q(room__topic__name__icontains=q))
    return render(request, 'base/home-page.html', context={
        'rooms': rooms,
        'topics': topics,
        'profile': profile,
        'r_messages': r_messages,
    })

My Models

class Profile(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE)
    profile_image = models.FileField(default='default.jpg', upload_to='profile_image', null=True)
    name = models.CharField(max_length=100, null=True, blank=True)
    bio = models.TextField(max_length=5000, null=True, blank=True)
    email = models.EmailField()
    username = models.CharField(max_length=100)

    def __str__(self):
        return f'{self.user}'

    def get_absolute_url(self):
        return reverse('user-profile', args=[str(self.user)])

class Topic(models.Model):
    name = models.CharField(max_length=200)
    user = models.ForeignKey(Profile, on_delete=models.CASCADE)

    def __str__(self):
        return f'{self.name}'


class Room(models.Model):
    host = models.ForeignKey(Profile, on_delete=models.SET_NULL, null=True)
    topic = models.ForeignKey(Topic, on_delete=models.SET_NULL, null=True)
    name = models.CharField(max_length=200)
    description = models.TextField(null=True, blank=True)
    participants = models.ManyToManyField(Profile, related_name='participants', blank=True)
    updated = models.DateTimeField(auto_now=True)  # Save every time when we update
    created = models.DateTimeField(auto_now_add=True)  # Only save when room was created

    class Meta:
        ordering = ['-updated', '-created']

    def __str__(self):
        return f'{self.name}'


class Message(models.Model):
    user = models.ForeignKey(Profile, on_delete=models.CASCADE)
    room = models.ForeignKey(Room, on_delete=models.CASCADE)
    body = models.TextField()
    updated = models.DateTimeField(auto_now=True)
    created = models.DateTimeField(auto_now_add=True)

    def __str__(self):
        return f'{self.body[:50]}'

    class Meta:
        ordering = ['-updated', '-created']

My Template

{% for room in rooms %}
        <li>
            {{ room.topic.host }}
            <a href="{% url 'home' %}?q={{ room.topic.name}}">{{ room.topic.name}}<span>{{ room.topic.room_set.all.count}}</span></a>
        </li>
{% endfor %}

queries When I try to use {{ room.topic.room_set.all.count}} and if there are 3 topics, the ORM will query 3 times to the DB to find each topic.rooms.count.

I know that the problem in this paro of code in template {{ room.topic.room_set.all.count}} but don't know how to fix it.

Answer 1

it is very simple all you need is to use select_related and prefetch_related in your quires to get all the data you need at once so your modified query in your view will be look like this:

 def home(request):
        ....
        rooms = Room.objects.select_related('host', 'topic').prefetch_related('participants').filter(
            Q(topic__name__icontains=q) |
            Q(name__icontains=q) |
            Q(description__icontains=q)
        ) # this is a new query
        topics = Topic.objects.select_related('user').prefetch_related('room_set') # this is a new query
        profile = Profile.objects.filter(user=request.user)
        r_messages = Message.objects.select_related('user', 'room').filter(Q(room__topic__name__icontains=q)) # this is a new query
        .....