CODEWITHSUNDEEP
pythondjango
proofit404
proofit404

Reputation: 386

How to optimize number of queries in the Django view?

I show comments in the topic detail view. Exactly two queries happens in this view: one to get topic and one to get comments list. I understand that select_related technique can't be used here since there are two different querysets created in the view. Is there any way to decrease number of queries to one?

Application code follows below.

app/models.py

class Topic(models.Model):

    headline = models.CharField(max_length=400)
    description = models.TextField()
    created = models.DateTimeField(auto_now_add=True)
    modified = models.DateTimeField(auto_now=True)
    author = models.ForeignKey(User, related_name='topics')

class Comment(models.Model):

    headline = models.CharField(blank=True, max_length=400)
    description = models.TextField()
    created = models.DateTimeField(auto_now_add=True)
    modified = models.DateTimeField(auto_now=True)
    author = models.ForeignKey(User, related_name='comments')
    topic = models.ForeignKey(Topic, related_name='comments')

app/views.py

class TopicDetail(DetailView):

    queryset = Topic.objects.select_related('author').all()
    context_object_name = 'topic'

    def get_context_data(self, **kwargs):

        context = super().get_context_data(**kwargs)
        topic = self.object
        context['comments'] = topic.comments.select_related('author').all()
        return context

app/templates/app/topic_detail.html

{{ topic.headline }}
{{ topic.description }}
{{ topic.created }}
{{ topic.modified }}
{{ topic.author }}
{% for comment in comments %}
    {{ comment.headline }}
    {{ comment.description }}
    {{ comment.author }}
{% endfor %}

Upvotes: 0

Views: 266

Answers (2)

przemo_li
przemo_li

Reputation: 4053

Even in pure SQL such "single" query would be hard.

Basically You would need to repeat all the topic data in each comment! Like in this pseudo code:

Select comment.*, topic.*
From comments
Right Join topic

That is massive data processing/transfer overhead. Whole operation should take more time, resources then separate queries.

If You really need it, then just write custom function in comment topic model, with pure SQL.

Upvotes: 1

Tanguy Serrat
Tanguy Serrat

Reputation: 322

Have a look at the prefetch_related method for reversed foreignKey lookups. This won't actually reduce the number of SQL queries, but django will merge in python related comments and Topics.

In the docs : https://docs.djangoproject.com/en/1.8/ref/models/querysets/#prefetch-related

Also, you should make sure to give different related_name to your fields, otherwise you'll have issues calling them :

author = models.ForeignKey(User, related_name='user_comments')
topic = models.ForeignKey(Topic, related_name='topic_comments')

This way you can write a single line query (but 2 SQL hits) :

topics = Topic.objects.prefetch_related('topic_comments').select_related('author').all()

Then for example :

{% for topic in topics %}
  {% for comment in topic.topic_comments.all %}
    {{ ... }}
  {% endfor %}
{% endfor %}

Upvotes: 4

Related Questions