I am making a blog website and I am facing some issues with the Query performance.

I have 3 models

1. User Model -> Users (To store user email, Password etc)
2. Post Model -> Actual Posts
3. people Model -> (To store users extra information)

Post Model ->

class Post(models.Model):
    user = models.ForeignKey(User, on_delete=models.PROTECT)
    category = models.ForeignKey(Category, on_delete=models.PROTECT)
    title = models.CharField(max_length=255,null=True)   
    description = models.CharField(max_length=1000,null=True)
    Likes = models.ManyToManyField(to=User, related_name='Post_likes')
    favourites = models.ManyToManyField(to=User,blank=True,related_name="favourite")

People Model ->

class People(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE)
    photo = models.ImageField(upload_to='profile_pics', blank=True,null=True)
    Phone_number = models.CharField(max_length=255,null=True,blank=True)
    Birth_Date = models.DateField(null=True,blank=True)
    Created_date = models.DateTimeField(auto_now_add=True)
    Updated_date = models.DateTimeField(auto_now=True)

Now as both of these models are connected to User model. I want to query the Post model and get the user photo in the template. Now when I use post.user.people.photo then for every post it generates a seperate query to DB resulting in slowness. I would like to use Join here to combines multiple tables and fetch all the records at once.

I am currently using following Query ->

posts =  Post.objects.select_related().prefetch_related('images_set').annotate(comments_Count = Count('comments_post',distinct=True)).annotate(Count('Likes',distinct=True)).all().order_by('-id')

CodePudding user response：

You can perform a .select_related(…) ^[Django-doc] on the user and the people with user__people, so:

posts = Post.objects.select_related(
    'user__people', 'category'
).prefetch_related('images_set').annotate(
    comments_Count = Count('comments_post',distinct=True),
    Count('Likes',distinct=True)
).order_by('-id')

Note: It is normally better to make use of the settings.AUTH_USER_MODEL ^[Django-doc] to refer to the user model, than to use the User model ^[Django-doc] directly. For more information you can see the referencing the User model section of the documentation.