CODEWITHSUNDEEP
pythonmysqlorminner-joindjango-select-related
akshay patil
akshay patil

Reputation: 1

How to create Django ORM query for Inner Join with Count clause?

I have 2 models named 1.Blogs/Posts 2. PostLike model. I want fetch all blogs with their respected likes counts as well. I have fetch all the blogs data with their likes count in Mysql but I am not aware about the DJANGO ORM query for same


from django.db import models
from django.contrib.auth.models import User
from django.utils import timezone

status = (('Public','Public'),('Private','Private'))

class Post(models.Model):
    title = models.CharField(max_length=50)
    description = models.TextField(max_length=50)
    content = models.TextField(max_length=250)
    owner = models.CharField(max_length=50)
    created_at = models.DateTimeField(default=timezone.now)
    updated_at = models.DateTimeField(default=timezone.now)
    status = models.CharField(max_length=10, default='Public', choices=status)

    def __str__(self):
        return f"id:{self.id},Title:{self.title},Desc:{self.description},Content:{self.content},Owner:{self.owner},Created_at:{self.created_at},Updated_at:{self.updated_at}"

    class Meta:
        ordering = ['-created_at']
        verbose_name_plural = "Posts"

class PostLike(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE,)
    post = models.ForeignKey(Post, on_delete=models.CASCADE, related_name='likes')
    created = models.DateTimeField(default=timezone.now)

    def __str__(self) -> str:
        return f"User:{self.user},Post:{self.user},created:{self.user},"

    class Meta:
        verbose_name_plural = 'PostLikes'

after firing following sql query i am able get likes count of blogs:

 SELECT posts_post.id, posts_post.title, posts_post.description, posts_post.content, posts_post.owner,posts_post.created_at,posts_post.updated_at,posts_post.status, COUNT(posts_postlike.id) FROM posts_post INNER JOIN posts_postlike ON posts_post.id = posts_postlike.post_id GROUP BY posts_post.id;

This is the output I am expecting.

+----+--------+--------------+----------+-------------+----------------------------+----------------------------+--------+--------------------------+
| id | title  | description  | content  | owner       | created_at                 | updated_at                 | status | COUNT(posts_postlike.id) |
+----+--------+--------------+----------+-------------+----------------------------+----------------------------+--------+--------------------------+
|  1 | title1 | description1 | content1 | ashwini@123 | 2023-05-14 16:34:03.991829 | 2023-05-14 16:34:03.991829 | Public |                        4 |
|  2 | title2 | description2 | content2 | ashwini@123 | 2023-05-14 16:35:06.173544 | 2023-05-14 16:35:06.173544 | Public |                        5 |
+----+--------+--------------+----------+-------------+----------------------------+----------------------------+--------+--------------------------+

Please someone suggest me Django ORM Query for same.

Upvotes: 0

Views: 25

Answers (1)

user2390182
user2390182

Reputation: 73460

You can use annotation:

from django.db.models import Count

posts = Post.objects.annotate(num_likes=Count("likes"))
for p in posts:
    # access as attribute
    p.num_likes

Upvotes: 0

Related Questions