Django ORM: filter related objects?

Question

Assuming I have a many-to-one relationship

class Author(model):
    name = TextField()

class Book(model):
    year = IntegerField()
    author = ForeignKey(Author)

you can easily filter from the "many" side. Eg, keep only the books whose author satisfies some condition

books.objects.filter(author__name__like='...')

How can I, from the Author side, keep (for each author) only the books that satisfy a condition? Eg. is there something like

Author.related_filter(book__year__gt>1800)

that would produce something like

select * from 
author join book on ...
where book.year > 1800

??

user2390182 · Accepted Answer · 2018-10-31 18:52:52Z

2

You can use prefetch_related with a custom Prefetch object:

authors = Author.objects.prefetch_related(
    Prefetch('book_set', queryset=Book.objects.filter(year>1800), to_attr='modern_books')
)

for author in authors:
    for book in author.modern_books.all():
        # do stuff

This should result in two queries total, so not quite the single SQL statement you are looking for, but the ORM's built-in way to fetch filtered related objects.

answered Oct 31, 2018 at 18:52

user2390182

73.7k6 gold badges71 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

blue note Over a year ago

thanks. If I just wanted to count the modern_books per author, is that efficient, or is there a more appropriate way??

Collectives™ on Stack Overflow

Django ORM: filter related objects?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related