Prefetch or annotate Django model with the foreign key of a related object

Question

Let's say we have the following models:

class Author(Model):
    ...
class Serie(Model):
    ...
class Book(Model):
    authors = ManyToManyField(Author, related_name="books")
    serie = ForeignKey(Serie)
    ...

How can I get the list of authors, with their series ?

I tried different combinations of annotate and prefetch:

---

list_authors = Author.objects.prefetch(Prefetch("books__series", queryset=Serie.objects.all(), to_attr="series"))

Trying to use list_authors[0].series throws an exception because Author has no series field

---

list_authors = Author.objects.annotate(series=FilteredExpression("books__series", condition=Q(...))

Trying to use list_authors[0].series throws an exception because Author has no series field

---

list_authors = Author.objects.annotate(series=F('books__series'))

returns all possible combinations of (author, serie) that have a book in common

---

As I'm using PostgreSQL for my database, I tried:

from django.contrib.postgres.aggregates import ArrayAgg
...
list_authors = Author.objects.annotate(series=ArrayAgg('books__serie', distinct=True, filter=Q(...)))

It works fine, but returns only the id of the related objects.

list_authors = Author.objects.annotate(series=ArrayAgg(
    Subquery(
        Serie.objects.filter(
            livres__auteurs=OuterRef('pk'),
            ...
        ).prefetch_related(...)
    )
 ))

fails because it needs an output_field, and a Model is not a valid value for output_field

BUT I can get the number of series for an author, so why not the actual list of them:

list_authors = Author.objects.annotate(nb_series=Count("books__series", filter=Q(...), distinct=True)
list_authors[0].nb_series
>>> 2

Thus I assume that what I try to do is possible, but I am at a loss regarding the "How"...

Answer 1

I don't think you can do this with an annotation on the Author queryset - as you've already found you can do F('books__series') but that will not return distinct results. Annotations generally only make sense if the result is a single value per row.

What you could do instead is have a method on the Author model that fetches all the series for that author with a relatively simple query. This will mean one additional query per author, but I can't see any alternative. Something like this:

class Author:

    def get_series(self):
        return Serie.objects.filter(book__authors=self).distinct()

Then you just do:

list_authors = Author.objects.all()
list_authors[0].get_series()