Optimizing Django queries - reducing database requests & proper queryset access

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.

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
.....