将相关项目的逗号分隔列表注释到Django queryset上 [英] Annotate a comma-separated list of related items onto Django queryset

问题描述

从表面上看，这似乎是一件很奇怪的事情，但是我需要一个逗号分隔的相关项目的字符串。以任何教程中的Author / Book模型为例，这是我目前正在做的事情：

On the face of it, this might seem like a weird thing to request but I need a comma-separated string of related items for a model. Taking the Author/Book model example from any tutorial, here's what I'm currently doing:

authors = Authors.objects.all().prefetch_related('books')
for author in authors:
    author.book_titles = ', '.join([book.title for book in author.books.all()])

这绝不算重，但是感觉很多余。就像数据库可能正在做的那样。在理想的世界中，我觉得我应该可以使用以下这些新颖的数据库功能。这是一个幻想示例，使用称为 Joiner（..）的虚函数：

It's by no means heavy, but it feels redundant. Like the database could be doing it. In an ideal world, I feel like I should able to annotate this on with one of these fancy new database functions. Here's a fantasy example, using a made-up function called Joiner(..):

Authors.objects.annotate(book_titles=Joiner('books__title', separator=', ')

有可能吗？

推荐答案

from django.db.models import Aggregate, CharField, Value

class GroupConcat(Aggregate):
    function = 'GROUP_CONCAT'
    template = '%(function)s(%(expressions)s)'

    def __init__(self, expression, delimiter, **extra):
        output_field = extra.pop('output_field', CharField())
        delimiter = Value(delimiter)
        super(GroupConcat, self).__init__(
            expression, delimiter, output_field=output_field, **extra)

    def as_postgresql(self, compiler, connection):
        self.function = 'STRING_AGG'
        return super(GroupConcat, self).as_sql(compiler, connection)

用法：

Author.objects.annotate(book_titles=GroupConcat('book__title', ', '))

自定义聚合。这应该适用于SQLite，MySQL和PostgreSQL。

Custom aggregates. This should work on SQLite, MySQL and PostgreSQL.

这篇关于将相关项目的逗号分隔列表注释到Django queryset上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！