Django个人博客搭建教程---annotate

需求

实现一个接口，查询每个分类下的文章数量

表结构

文章和分类是1对多的关系

文章表

class Articles(models.Model):
    id = models.AutoField(primary_key=True, db_index=True)  # id
    title = models.CharField(max_length=150)  # 博客标题
    # body = models.TextField()  # 博客正文
    body = MDTextField()
    timestamp = models.DateTimeField()  # 创建时间
    authorname = models.ForeignKey('blog.BlogUser', on_delete=models.CASCADE)  # 作者姓名
    views = models.PositiveIntegerField(default=0)
    category = models.ForeignKey(Category, on_delete=models.CASCADE, primary_key=False)
    tags = models.ManyToManyField(Tag, blank=True, null=True)
    greats = models.PositiveIntegerField(default=0)
    comments = models.IntegerField(default=0)
    status = models.CharField(max_length=20, default="DEL")
    brief = models.CharField(max_length=200, blank=True, null=True)
    pic = models.ImageField(upload_to='jiablogimages')
    istop = models.CharField(max_length=5, default='', null=True, blank=True)
    articlebodybrief = models.TextField(blank=True, null=True)
    last_edit_timestamp = models.DateTimeField(auto_now=True, verbose_name="更新时间", editable=True)
    url_slug = models.SlugField(editable=False, max_length=200)
    rand_id = models.CharField(max_length=8, default="1a2b3c4d")

    pic_800_450 = ImageSpecField(
        source="pic",
        processors=[ResizeToFill(800, 450)],
        format='JPEG',
        options={'quality': 95}
    )

    # 访问量
    def increase_views(self):
        self.views += 1
        self.save(update_fields=['views'])
        return self.views

    @property
    def all_comments(self):
        return self.comment_set.all()

    def article_greats(self):
        return self.greats

    class Meta:
        verbose_name = '文章'
        verbose_name_plural = '文章'
        unique_together = ("category", "id", "title")

    def save(self, *args, **kwargs):
        self.url_slug = slugify(self.title)
        if self.rand_id == "" or self.rand_id == "1a2b3c4d":
            self.rand_id = ''.join(random.sample(string.ascii_letters + string.digits, 8))
        super(Articles, self).save(*args, **kwargs)

    @property
    def body_html(self):
        return self.rich_content.get("body", "")

    @cached_property
    def rich_content(self):
        return generate_rich_content(self.body)

分类表

class Category(models.Model):
    """
        Django 要求模型必须继承 models.Model 类。
        Category 只需要一个简单的分类名 name 就可以了。
        CharField 指定了分类名 name 的数据类型，CharField 是字符型，
        CharField 的 max_length 参数指定其最大长度，超过这个长度的分类名就不能被存入数据库。
        当然 Django 还为我们提供了多种其它的数据类型，如日期时间类型 DateTimeField、整数类型 IntegerField 等等。
        Django 内置的全部类型可查看文档：
        https://docs.djangoproject.com/en/1.10/ref/models/fields/#field-types
        """
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=100)

    class Meta:
        verbose_name = "分类"
        verbose_name_plural = verbose_name

    def natural_key(self):
        return self.__str__()

    def catcount(self):
        return Articles.objects.filter(category__name__exact=self.name).filter(status='有效').count()

    def __str__(self):
        return self.name

实现

这里需要加上过滤器对文章进行筛选

@require_http_methods(["GET"])
def get_categroy_all(request):
    category_list = Category.objects.values('name').annotate(
        num_articles=Count('articles', filter=Q(articles__status='有效')))
    data = {}
    try:
        list = []
        for i in category_list:
            p_tmp = {
                "name": i['name'],
                "num_articles": i['num_articles']
            }
            list.append(p_tmp)
        data['list'] = list
        data['msg'] = 'success'
        data['error_num'] = 0
    except Exception as e:
        data['msg'] = str(e)
        data['error_num'] = 1
    return HttpResponse(json.dumps(data), content_type='application/json')

结果

{
    "list": [
        {
            "name": "Django",
            "num_articles": 28
        },
        {
            "name": "Python",
            "num_articles": 24
        },
        {
            "name": "Leetcode",
            "num_articles": 7
        },
        {
            "name": "Mysql",
            "num_articles": 5
        },
        {
            "name": "Other",
            "num_articles": 18
        },
        {
            "name": "Java",
            "num_articles": 19
        },
        {
            "name": "JavaScript",
            "num_articles": 2
        },
        {
            "name": "Vue.js",
            "num_articles": 2
        },
        {
            "name": "Collectd",
            "num_articles": 1
        },
        {
            "name": "操作系统",
            "num_articles": 1
        },
        {
            "name": "Web安全",
            "num_articles": 1
        },
        {
            "name": "Redis",
            "num_articles": 1
        },
        {
            "name": "GIT",
            "num_articles": 2
        },
        {
            "name": "Golang",
            "num_articles": 1
        }
    ],
    "msg": "success",
    "error_num": 0
}