CODEWITHSUNDEEP
djangodjango-modelsdjango-viewsdjango-templatesdjango-orm
Tushar Sethi
Tushar Sethi

Reputation: 344

Query Performance Optimization in Django

I am creating a Blog website in django and while creating a page for specific blog posts I came across a problem in querying the data using the ORM.

I have a model for Post which is related to another model comments where all the comments are captured. The comments model have following fields ->

class Comment(models.Model):
    comment = models.TextField(null=True)
    Created_date = models.DateTimeField(auto_now_add=True)
    Updated_date = models.DateTimeField(auto_now=True)
    post = models.ForeignKey(Post, on_delete=models.CASCADE, related_name='comments_post')
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name='comments_user')

    def __str__(self):
        return self.comment

Now while Querying the single blog post there could be N number of comments and for each comment there is a user assigned who has written the comment. So I want to find the user details such (name, email or phone number) for the user for each comment.

The Query that I have executed is ->

post = Post.objects.select_related().prefetch_related('images_set','comments_post').get(id=post_id)

Now when I try to use {{comment.user.email}} and if there are 3 comments for the post, the ORM will query 3 times to the DB to find the email for each user. But if I use {{comment.user_id}} it will query only once as it preloads the user_id as it is available in comments table.

Is there any way I can make only one query to the DB to get all the Names for all the comments?

Upvotes: 1

Views: 857

Answers (2)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476503

You can save an extra query to the database that fetches the user with a Prefetch object [Django-doc]:

from django.db.models import Prefetch

post = Post.objects.select_related().prefetch_related(
    'images_set',
    Prefetch('comments_post', Comment.objects.select_related('user'))
).get(id=post_id)

This will make three roundtrips to the database: one for the Post, one for the image_set, and one for the comments_post. The last one will also fetch the data of the user, to prevent making an extra query for that.

Upvotes: 1

Javohir Elmurodov
Javohir Elmurodov

Reputation: 143

It's possible prefetch_related foreign key inside foreign key. Such as prefetch_related('comments_post__user'), or If you want more performance you can use annotate. Annotate required field, It's more faster than prefetch

Upvotes: 5

Related Questions