Annotate a comma-separated list of related items onto Django queryset

Question

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()])

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=', ')

Is that possible? If so, how?

Answer 1

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)

Usage:

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

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