Django ORM Query Optimization Issue

Question

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

I have 3 models

User Model -> Users (To store user email, Password etc)
Post Model -> Actual Posts
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')

willeM_ Van Onsem · Accepted Answer · 2022-03-13 17:36:28Z

3

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.

edited Mar 13, 2022 at 17:36

answered Mar 13, 2022 at 17:28

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Django ORM Query Optimization Issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related