Filtering a django Prefetch based on more fields from the root object

Viewed 283

This is similar to other questions regarding complicated prefetches but seems to be slightly different. Here are some example models:

class Author(Model):
    pass

class Book(Model):
    authors = ManyToManyField(Author, through='BookAuthor')

class BookAuthor(Model):
    book = ForeignKey(Book, ...)
    author = ForeignKey(Author, ...)

class Event(Model):
    book = ForeignKey(Book, ...)
    author = ForeignKey(Author, ...)

In summary: a BookAuthor links a book to one of its authors, and an Event also concerns a book and one of its authors (the latter constraint is unimportant). One could design the relationships differently but it's too late for that now, and in my case, this in fact is part of migrating away from the current setup.

Now suppose I have a BookAuthor instance and want to find any events that relate to that combination of book and author. I can follow either the book or author relations on BookAuthor and their event_set reverse relationship, but neither gives me what I want and, if I have several BookAuthors it becomes a pain.

It seems that the way to get an entire queryset "annotated" onto a model instance is via prefetch_related but as far as I can tell there is no way, in the Prefetch object's queryset property, to refer to the root object's fields. I would like to do something like this:

BookAuthor.objects.filter(...).prefetch_related(
    Prefetch(
        'book__event_set',
        queryset=Event.objects.filter(
            author=OuterRef('author')
        )
    }    
)

However, OuterRef only works in subqueries and this is not one. The answer to this question suggests I could use a subquery here but I don't understand how it could ever work: you have to put the subquery inside a query to work in the Prefetch, and the OuterRef refers to that object/DB row; there is no way to get back to the original, root object. If I translate the code there into my situation it is clear that the OuterRef is referring to the outer Event query, not the outer BookAuthor.

To make the question precise: what I want is O(1) queries which, for a queryset of BookAuthors annotate each instance - or one of its foreign keys - with a collection of the corresponding Events. Obviously one can get all the Events and glue everything together in python. I want to avoid this but if anyone has a particularly elegant way of doing that, it would also be useful to know.

0 Answers
Related