Query Performance Optimization in Django-CodePudding

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?

CodePudding user response：

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