Query from one side filtering on the many side.

Using Django
1

Django question

I have 2 models (Author and Book) related by a foreign key and I want to query all Authors and display them with their newest Book, here is a sample of my code:

# models.py

class Author(models.Model):
    name = models.CharField(max_length=200)

class Book(models.Model):
    title = models.CharField(max_length=200)
    published = models.DateField()
    author = models.ForeignKey(Author, on_delete=models.CASCADE)

# views.py

def index(request):
    authors = [
        (author, author.book_set.order_by('-published').first())
        for author in Author.objects.all()
    ]

    return render(request, 'index.html', {'authors': authors})

{# index.html #}

{% extends 'base.html' %}

{% block content %}
  {% for author, newest_book in authors %}
    <p>
      {{ newest_book.title }} by {{ author.name }}. Published on {{ newest_book.published }}
    </p>
  {% endfor %}
{% endblock %}

However, I don’t like this since this approach just queries the database countless times…

Also I would like to be able to add some filters for example to only display Authors with their latest Book if the latest Book was published within the last 30 days (I would need an inner query and a filter on the many side)…

2

You want to use prefetch_related with a custom Prefetch.

Something like:

authors = Author.objects.prefetch_related(newest_book_set=Prefetch('book_set', queryset=Book.objects.order_by('-published')[0]))

each author.newest_book_set will contain their single latest book, and this will run in 2 queries.

1 Like
3

Thanks! It seems like Prefetch holds a lot to help me, thanks!

I am just wondering if it is possible to apply a filter on the Prefetch which will propagate up to the Author (i.e., would I be able to query for all authors with their latest book if the book was published within the last 30 days etc.)

4

No, the filters applied in Prefetch do not impact the original QuerySet's filters.