# Query from one side filtering on the many side.

**URL:** <https://forum.djangoproject.com/t/query-from-one-side-filtering-on-the-many-side/5781>\
**Category:** Using Django\
**Created:** [December 20, 2020, 4:43pm UTC](https://forum.djangoproject.com/t/query-from-one-side-filtering-on-the-many-side/5781 "2020-12-20T16:43:16Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![yankyhoffman](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/yankyhoffman/32/2317_2.png) [@yankyhoffman](https://forum.djangoproject.com/u/yankyhoffman)\
**Post date:** [December 20, 2020, 4:43pm UTC](https://forum.djangoproject.com/t/query-from-one-side-filtering-on-the-many-side/5781/1 "2020-12-20T16:43:16Z")

</div>

Django question

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

```python
# 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})

```

```auto
{# 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 `Author`s 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)…

---

<div class="post-metadata">

**Author:** ![adamchainz](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/adamchainz/32/26_2.png) [@adamchainz](https://forum.djangoproject.com/u/adamchainz)\
**Post date:** [December 20, 2020, 5:11pm UTC](https://forum.djangoproject.com/t/query-from-one-side-filtering-on-the-many-side/5781/2 "2020-12-20T17:11:02Z")

</div>

You want to use `prefetch_related` with a custom `Prefetch`.

> **[QuerySet API reference | Django documentation | Django](https://docs.djangoproject.com/en/3.1/ref/models/querysets/#django.db.models.Prefetch)**

Something like:

```auto
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.

---

<div class="post-metadata">

**Author:** ![yankyhoffman](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/yankyhoffman/32/2317_2.png) [@yankyhoffman](https://forum.djangoproject.com/u/yankyhoffman)\
**Post date:** [December 20, 2020, 5:22pm UTC](https://forum.djangoproject.com/t/query-from-one-side-filtering-on-the-many-side/5781/3 "2020-12-20T17:22:39Z")

</div>

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.)

---

<div class="post-metadata">

**Author:** ![CodenameTim](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/codenametim/32/31507_2.png) [@CodenameTim](https://forum.djangoproject.com/u/CodenameTim)\
**Post date:** [December 20, 2020, 5:27pm UTC](https://forum.djangoproject.com/t/query-from-one-side-filtering-on-the-many-side/5781/4 "2020-12-20T17:27:47Z")

</div>

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