主页 / 问题 / 1089348
Accepted
Artyom Lazovikov
Asked:2020-03-01 20:44:30 +0000 UTC2020-03-01 20:44:30 +0000 UTC

为类别表中的每条记录显示新闻表中的记录数

  • 772

为类别表中的每条记录显示新闻表中的记录数。按类别名称升序排列记录

M2M 通信

您好，ORM 有问题

from django.db import models
from django.conf import settings


class Category(models.Model):
    name = models.CharField(max_length=200)

    def __str__(self):
        return self.name

class News(models.Model):
    title = models.CharField(max_length=200)
    body = models.TextField()
    is_published = models.BooleanField(default=False)
    category = models.ManyToManyField(Category)

    def publish(self):
        self.is_published = True
        self.save()

    def __str__(self):
        return self.title

我可以写一个对 posgresql 的请求，但我仍然不知道如何将它传输到 django ORM。

SELECT ct.name, count(n.category_id) FROM category ct 
LEFT JOIN news n ON ct.id=n.category_id 
GROUP BY ct.name, category_id 
ORDER BY category_id DESC

我不明白如何使用聚合函数并从选择中获取必要的字段

django

1 个回答

  1. Best Answer

    如果需要按类别名称升序排序，查询将如下所示：

    SELECT "category"."name" AS "name", COUNT("news"."id") AS "cnt"
FROM "news"
         LEFT OUTER JOIN "news_category" ON ("news"."id" = "news_category"."news_id")
         LEFT OUTER JOIN "category" ON ("news_category"."category_id" = "category"."id")
GROUP BY "category"."name"
ORDER BY "category"."name" ASC;

    甲骨文：

    queryset = (
    News
    .objects
    .values('category__name')
    .annotate(name=F('category__name'))
    .annotate(cnt=Count('id'))
    .order_by('category__name')
    .values('name', 'cnt')
)
    • 1

相关问题