'Problem with aggregation by annotated fields

I have models:

class Publisher(Model):
    name = TextField()

class Author(Model):
    name = TextField()

class Book(Model):
    publisher = ForeignKey("Publisher")
    author = ForeignKey("Author")

class Magazine(Model):
    publisher = ForeignKey("Publisher")
    writer = ForeignKey("Author")

I want to know which authors wrote for publishers. My version is this:

from django.db.models import TextField, F, Subquery, OuterRef
from django.contrib.postgres.aggregates import StringAgg  # I use postgres

# to lead to the same name
books = Book.objects.annotate(author_name=F("author__name"))
magazines = Magazine.objects.annotate(author_name=F("writer__name"))

books = books.values("publisher_id", "author_name")
magazines = magazines.values("publisher_id", "author_name")
product = books.union(magazines)

# !! here I have a problem with grouping
product = product.group_by(
    "publisher_id"
).annonate(
    author_names=StringAgg("author_name", ";")
)

publishers = Publisher.objects.all().annotate(
    author_names=Subquery(
        product.filter(publisher_id=OuterRef("id")).values("author_names")[:1],
        output_field=TextField()
    )
)

# I was expecting something like
#     name    |       author_names
# ------------------------------------------
#  Publisher1 | Author1;Author2;Author3
#  Publisher2 | Author2
#  Publisher3 | Author2;Author3

The problem is that QuerySet has no .group_by() method, instead the .values() method is suggested (product.values("publisher_id").annonate(...)).

But this is complicated by the fact that I had previously called .values("publisher_id", "author_name") to bring two different models into the same view.

I also tried using .only("publisher_id", "author_name"), but (maybe it's a Django bug) this method can't work together with annotated and normal fields.

Is there any way to fix this problem or some other way to get a list of authors for a publisher?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source