Django 和条件聚合

发布于 2024-09-02 16:00:37 字数 690 浏览 10 评论 0原文

我有两个模型、作者和文章：

class Author(models.Model):
    name = models.CharField('name', max_length=100)

class Article(models.Model)
    title = models.CharField('title', max_length=100)
    pubdate = models.DateTimeField('publication date')
    authors = models.ManyToManyField(Author)

现在我想选择所有作者并用各自的文章数对他们进行注释。对于 Django 的聚合来说，这是小菜一碟。问题是，它应该只计算已经发表的文章。根据 Django 票证跟踪器中的票证 11305，这还不可能。我尝试使用该票证中提到的 CountIf 注释，但它没有引用日期时间字符串，也没有进行所需的所有连接。

那么，除了编写自定义 SQL 之外，最好的解决方案是什么？

原文

I have two models, authors and articles:

class Author(models.Model):
    name = models.CharField('name', max_length=100)

class Article(models.Model)
    title = models.CharField('title', max_length=100)
    pubdate = models.DateTimeField('publication date')
    authors = models.ManyToManyField(Author)

Now I want to select all authors and annotate them with their respective article count. That's a piece of cake with Django's aggregates. Problem is, it should only count the articles that are already published. According to ticket 11305 in the Django ticket tracker, this is not yet possible. I tried to use the CountIf annotation mentioned in that ticket, but it doesn't quote the datetime string and doesn't make all the joins it would need.

So, what's the best solution, other than writing custom SQL?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

站稳脚跟 2024-09-09 16:00:38

Django 1.8+ 解决方案

从 Django 1.8 开始，条件表达式是可用于构建查询集。

有关更多详细信息，请参阅文档，但您的问题的快速解决方案如下所示：

today = datetime.date.today()
authors = Author.objects.all().annotate(article_count=Sum(
    Case(When(articles__pubdate__lt=today, then=1),
         output_field=IntegerField())
))

虽然我没有检查它，但它应该可以工作。

Django 1.8+ solution

Since Django 1.8, conditional expressions are available for building querysets.

For more details consult the documentation, but a fast solution for your question would look something like:

today = datetime.date.today()
authors = Author.objects.all().annotate(article_count=Sum(
    Case(When(articles__pubdate__lt=today, then=1),
         output_field=IntegerField())
))

I didn't check it though, but it should work.

回复收藏 0 原文

只有一腔孤勇 2024-09-09 16:00:38

我通过使用所需的 GROUP BY 语句创建一个 SQL 视图，以及使用 managed = False 和 OneToOneField 的所述视图模型解决了我的问题到 Author 表。这不是最有效或最优雅的解决方案，但它工作正常。

回复收藏 0 原文

-黛色若梦 2024-09-09 16:00:37

您可以使用 django-aggregate-if 应用程序，其灵感来自票证 11305。
或者您可以只使用查询集的 extra 方法（假设您的应用程序名为“articles”）：

Author.objects.all().extra(
    select={'article_count': 'SELECT COUNT(*) FROM "articles_article" '
                             'INNER JOIN "articles_article_authors" '
                             'ON "articles_article"."id" = '
                             '   "articles_article_authors"."article_id" '
                             'WHERE "articles_article_authors"."author_id" = '
                             '      "articles_author"."id" '
                             'AND "articles_article"."pubdate" IS NOT NULL'})

You could use django-aggregate-if application, that inspired by ticket 11305.
Or you can just use the extra method for queryset (suppose your application named "articles"):

Author.objects.all().extra(
    select={'article_count': 'SELECT COUNT(*) FROM "articles_article" '
                             'INNER JOIN "articles_article_authors" '
                             'ON "articles_article"."id" = '
                             '   "articles_article_authors"."article_id" '
                             'WHERE "articles_article_authors"."author_id" = '
                             '      "articles_author"."id" '
                             'AND "articles_article"."pubdate" IS NOT NULL'})

回复收藏 0 原文

~没有更多了~