python 如何创建Django ORM查询Inner Join with Count子句?

mklgxw1f  于 2023-05-16  发布在  Python
关注(0)|答案(1)|浏览(105)

我有2名模型1.博客/职位2. PostLike模型。我想获取所有的博客与他们的尊重喜欢计数以及。我已经取得了所有的博客数据与他们喜欢的计数在Mysql,但我不知道有关的Django ORM查询相同

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'

在启动以下SQL查询后,我可以得到喜欢的博客数量:

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;

这是我所期待的输出。

+----+--------+--------------+----------+-------------+----------------------------+----------------------------+--------+--------------------------+
| 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 |
+----+--------+--------------+----------+-------------+----------------------------+----------------------------+--------+--------------------------+

请有人建议我Django ORM查询相同。

ejk8hzay

ejk8hzay1#

您可以使用注解:

from django.db.models import Count

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

相关问题