### Author:  [@JonnyWaffles](https://forum.djangoproject.com/u/JonnyWaffles)
#### Post date: [March 21, 2025, 6:00pm UTC](https://forum.djangoproject.com/t/prefetch-top-n-most-recent-related-objects/39767/1 "2025-03-21T18:00:02Z")
Hi friends, I understand I can use subqueries and annotations to select the most recent related object’s attribution, but since subqueries cannot return objects, only single columns, I’m trying to think through how to “select the N most recent related objects” for every record in a QuerySet leveraging the ORM.
For example, consider Many Documents to One Ticket model. We can get the latest document id for each ticket easily
```python
latest_documents = Docs.objects.filter(id_main=OuterRef("id")).order_by(
"-tstamp"
)
Ticket.objects.annotate(
latest_document_id=Subquery(latest_documents.values("document_id")[:1])
)
```
But what if I want the entire related object? I understand I can use `Prefetch` to get all the related `Ticket.docs`, but after many hours of Googling and experimentation I don’t understand how to limit the prefetch operation to the top 1 or 2 most recent documents for each Ticket.
Consider a Ticket List view and I want to show the top two latest document names and types for every ticket in the same grid. The best I could come up with is a creating a `Prefetch(queryset=)` where `qs` is a Window function filtered rank, something like
```python
Docs.objects.annotate(
row_number=Window(
expression=RowNumber(),
partition_by=[F("ticket_id")],
order_by="-tstamp")
).filter(
row_number__lte=2
)
```
But that doesn’t feel great either since most databases aren’t great at Window function filtering optimizations (Snowflake’s Qualify By rules).
Anyway, filtering the top N related objects seems like a common task, so I’m clearly missing something! Thanks for any help.
Cheers.
---
### Author:  [@charettes](https://forum.djangoproject.com/u/charettes)
#### Post date: [March 21, 2025, 6:14pm UTC](https://forum.djangoproject.com/t/prefetch-top-n-most-recent-related-objects/39767/2 "2025-03-21T18:14:58Z")
Assuming you’re on Django 4.2+ you should be able to simply use `Prefetch` with a sliced queryset and [it will use the proper window functions for you](https://github.com/django/django/commit/242499f2dc2bf24a9a5c855690a2e13d3303581a).
```python
Ticket.objects.prefetch_related(
Prefetch(
"documents",
Document.objects.order_by("-timestamp")[:n]
to_attr="latest_documents",
)
)
```
---
### Author:  [@JonnyWaffles](https://forum.djangoproject.com/u/JonnyWaffles)
#### Post date: [March 21, 2025, 6:16pm UTC](https://forum.djangoproject.com/t/prefetch-top-n-most-recent-related-objects/39767/3 "2025-03-21T18:16:13Z")
Cheers thanks Charettes. I’ll try it out. I haven’t used Django in half a decade, but I remember you helping me out long ago. Hope you and yours are well!
---
### Author:  [@OttoAndrey](https://forum.djangoproject.com/u/OttoAndrey)
#### Post date: [April 17, 2025, 11:21am UTC](https://forum.djangoproject.com/t/prefetch-top-n-most-recent-related-objects/39767/4 "2025-04-17T11:21:08Z")
I’ve got `TypeError: Cannot filter a query once a slice has been taken.`
---
### Author:  [@charettes](https://forum.djangoproject.com/u/charettes)
#### Post date: [April 17, 2025, 1:44pm UTC](https://forum.djangoproject.com/t/prefetch-top-n-most-recent-related-objects/39767/5 "2025-04-17T13:44:03Z")
It’s impossible to provide you any support @OttoAndrey as you didn’t provide a full traceback, a piece of code and associated set of models to reproduce.
As mentioned above the features is only available in Django 4.2+ and is tested against [many-to-many fields in both directions](https://github.com/django/django/commit/242499f2dc2bf24a9a5c855690a2e13d3303581a) as well as [reverse foreign keys](https://github.com/django/django/commit/242499f2dc2bf24a9a5c855690a2e13d3303581a#diff-285fd45fbbf8753124b2a81fcc5d6cc1d55237d192efb57f1ca46c3b2e7abaf5R1940-R1961) which are the only multi-valued relationships Django exposes where this feature makes sense.
---
### Author:  [@OttoAndrey](https://forum.djangoproject.com/u/OttoAndrey)
#### Post date: [April 18, 2025, 9:15am UTC](https://forum.djangoproject.com/t/prefetch-top-n-most-recent-related-objects/39767/6 "2025-04-18T09:15:34Z")
You are right. I’m using Django 5.2.
For example I have three models: `User`, `Post`, `Like`.
Post has fk to User. Post has GenericRelation to Like.
If I do
```python
User.objects.prefetch_related(Prefetch("posts", queryset=Post.objects.order_by("-pk")[:1], to_attr="latest_posts"))
```
It is working as expected. Each user will be with one or zero posts.
If I do the same for Post-Like
```python
Post.objects.prefetch_related(Prefetch("likes", queryset=Like.objects.order_by("-pk")[:1], to_attr="last_likes"))
```
> **Models**
>
> ```python
> from django.conf import settings
> from django.contrib.auth.models import AbstractBaseUser, PermissionsMixin
> from django.contrib.auth.models import UserManager as DjangoUserManager
> from django.contrib.contenttypes.fields import GenericRelation, \
> GenericForeignKey
> from django.db import models
> from django.utils.translation import gettext_lazy as _
>
> import citext
>
> class UserManager(DjangoUserManager):
> """Adjusted user manager that works w/o `username` field."""
>
> def _create_user(
> self,
> email: str,
> password: str | None,
> **extra_fields,
> ) -> "User": # pragma: no cover
> """Create and save a user with the given email and password."""
> if not email:
> raise ValueError("The given email must be set")
> email = self.normalize_email(email)
> user = self.model(email=email, **extra_fields)
> user.set_password(password)
> user.save(using=self._db)
> return user
>
> def create_superuser(
> self,
> email: str,
> password: str | None = None,
> **extra_fields,
> ) -> "User": # pragma: no cover
> """Create superuser instance (used by `createsuperuser` cmd)."""
> extra_fields.setdefault("is_staff", True)
> extra_fields.setdefault("is_superuser", True)
>
> if extra_fields.get("is_staff") is not True:
> raise ValueError("Superuser must have is_staff=True.")
> if extra_fields.get("is_superuser") is not True:
> raise ValueError("Superuser must have is_superuser=True.")
>
> return self._create_user(email, password, **extra_fields)
>
> class User(
> models.Model,
> AbstractBaseUser,
> PermissionsMixin,
> ):
> """Custom user model without username."""
>
> first_name = models.CharField(
> verbose_name=_("First name"),
> max_length=30,
> blank=True,
> )
> last_name = models.CharField(
> verbose_name=_("Last name"),
> max_length=30,
> blank=True,
> )
> email = citext.CIEmailField(
> verbose_name=_("Email address"),
> max_length=254, # to be compliant with RFCs 3696 and 5321
> unique=True,
> )
> is_staff = models.BooleanField(
> verbose_name=_("Staff status"),
> default=False,
> help_text=_(
> "Designates whether the user can log into this admin site.",
> ),
> )
> is_active = models.BooleanField(
> verbose_name=_("Active"),
> default=True,
> help_text=_(
> "Designates whether this user should be treated as active.",
> ),
> )
>
> avatar = models.ImageField(
> verbose_name=_("Avatar"),
> blank=True,
> null=True,
> upload_to=settings.DEFAULT_MEDIA_PATH,
> max_length=512,
> )
>
> EMAIL_FIELD = "email"
> USERNAME_FIELD = "email"
> REQUIRED_FIELDS = []
>
> objects = UserManager()
>
> class Meta:
> verbose_name = _("User")
> verbose_name_plural = _("Users")
>
> def __str__ (self) -> str:
> return self.email
>
> class Post(models.Model):
>
> user = models.ForeignKey(
> to="users.User",
> on_delete=models.CASCADE,
> related_name="posts",
> )
>
> likes = GenericRelation(
> to="users.Like",
> related_query_name="post",
> )
>
> body = models.TextField()
>
> class Like(models.Model):
>
> user = models.ForeignKey(
> to="users.User",
> on_delete=models.CASCADE,
> related_name="likes",
> )
>
> content_type = models.ForeignKey(
> to="contenttypes.ContentType",
> verbose_name=_("Content type of object"),
> on_delete=models.CASCADE,
> limit_choices_to={
> "model__in": [
> Post._meta.model_name,
> ],
> },
> )
> object_id = models.PositiveBigIntegerField(
> verbose_name=_("Related object ID"),
> )
> content_object = GenericForeignKey(
> ct_field="content_type",
> fk_field="object_id",
> )
>
> ```
> **Traceback**
>
> ```bash
> In [16]: Post.objects.prefetch_related(Prefetch("likes", queryset=Like.objects.order_by("-pk")[:1], to_attr="last_likes"))
>
> Out[16]: SELECT "users_post"."id",
> "users_post"."created",
> "users_post"."modified",
> "users_post"."user_id",
> "users_post"."body"
> FROM "users_post"
> LIMIT 21
>
> Execution time: 0.004464s [Database: default]
> ---------------------------------------------------------------------------
> TypeError Traceback (most recent call last)
> File ~/work_projects/saritasa-python-boilerplate-django/.venv/lib/python3.13/site-packages/IPython/core/formatters.py:770, in PlainTextFormatter. __call__ (self, obj)
> 763 stream = StringIO()
> 764 printer = pretty.RepresentationPrinter(stream, self.verbose,
> 765 self.max_width, self.newline,
> 766 max_seq_length=self.max_seq_length,
> 767 singleton_pprinters=self.singleton_printers,
> 768 type_pprinters=self.type_printers,
> 769 deferred_pprinters=self.deferred_printers)
> --> 770 printer.pretty(obj)
> 771 printer.flush()
> 772 return stream.getvalue()
>
> File ~/work_projects/saritasa-python-boilerplate-django/.venv/lib/python3.13/site-packages/IPython/lib/pretty.py:419, in RepresentationPrinter.pretty(self, obj)
> 408 return meth(obj, self, cycle)
> 409 if (
> 410 cls is not object
> 411 # check if cls defines __repr__
> (...)
> 417 and callable(_safe_getattr(cls, " __repr__", None))
> 418 ):
> --> 419 return _repr_pprint(obj, self, cycle)
> 421 return _default_pprint(obj, self, cycle)
> 422 finally:
>
> File ~/work_projects/saritasa-python-boilerplate-django/.venv/lib/python3.13/site-packages/IPython/lib/pretty.py:794, in _repr_pprint(obj, p, cycle)
> 792 """A pprint that just redirects to the normal repr function."""
> 793 # Find newlines and replace them with p.break_()
> --> 794 output = repr(obj)
> 795 lines = output.splitlines()
> 796 with p.group():
>
> File ~/work_projects/saritasa-python-boilerplate-django/.venv/lib/python3.13/site-packages/django/db/models/query.py:376, in QuerySet. __repr__ (self)
> 375 def __repr__ (self):
> --> 376 data = list(self[: REPR_OUTPUT_SIZE + 1])
> 377 if len(data) > REPR_OUTPUT_SIZE:
> 378 data[-1] = "...(remaining elements truncated)..."
>
> File ~/work_projects/saritasa-python-boilerplate-django/.venv/lib/python3.13/site-packages/django/db/models/query.py:400, in QuerySet. __iter__ (self)
> 385 def __iter__ (self):
> 386 """
> 387 The queryset iterator protocol uses three nested iterators in the
> 388 default case:
> (...)
> 398 - Responsible for turning the rows into model objects.
> 399 """
> --> 400 self._fetch_all()
> 401 return iter(self._result_cache)
>
> File ~/work_projects/saritasa-python-boilerplate-django/.venv/lib/python3.13/site-packages/django/db/models/query.py:1930, in QuerySet._fetch_all(self)
> 1928 self._result_cache = list(self._iterable_class(self))
> 1929 if self._prefetch_related_lookups and not self._prefetch_done:
> -> 1930 self._prefetch_related_objects()
>
> File ~/work_projects/saritasa-python-boilerplate-django/.venv/lib/python3.13/site-packages/django/db/models/query.py:1320, in QuerySet._prefetch_related_objects(self)
> 1318 def _prefetch_related_objects(self):
> 1319 # This method can only be called once the result cache has been filled.
> -> 1320 prefetch_related_objects(self._result_cache, *self._prefetch_related_lookups)
> 1321 self._prefetch_done = True
>
> File ~/work_projects/saritasa-python-boilerplate-django/.venv/lib/python3.13/site-packages/django/db/models/query.py:2380, in prefetch_related_objects(model_instances, *related_lookups)
> 2377 obj_to_fetch = [obj for obj in obj_list if not is_fetched(obj)]
> 2379 if obj_to_fetch:
> -> 2380 obj_list, additional_lookups = prefetch_one_level(
> 2381 obj_to_fetch,
> 2382 prefetcher,
> 2383 lookup,
> 2384 level,
> 2385 )
> 2386 # We need to ensure we don't keep adding lookups from the
> 2387 # same relationships to stop infinite recursion. So, if we
> 2388 # are already on an automatically added lookup, don't add
> 2389 # the new lookups from relationships we've seen already.
> 2390 if not (
> 2391 prefetch_to in done_queries
> 2392 and lookup in auto_lookups
> 2393 and descriptor in followed_descriptors
> 2394 ):
>
> File ~/work_projects/saritasa-python-boilerplate-django/.venv/lib/python3.13/site-packages/django/db/models/query.py:2544, in prefetch_one_level(instances, prefetcher, lookup, level)
> 2523 # prefetcher must have a method get_prefetch_querysets() which takes a list
> 2524 # of instances, and returns a tuple:
> 2525
> (...)
> 2533 # The 'values to be matched' must be hashable as they will be used
> 2534 # in a dictionary.
> 2536 if hasattr(prefetcher, "get_prefetch_querysets"):
> 2537 (
> 2538 rel_qs,
> 2539 rel_obj_attr,
> 2540 instance_attr,
> 2541 single,
> 2542 cache_name,
> 2543 is_descriptor,
> -> 2544 ) = prefetcher.get_prefetch_querysets(
> 2545 instances, lookup.get_current_querysets(level)
> 2546 )
> 2547 else:
> 2548 warnings.warn(
> 2549 "The usage of get_prefetch_queryset() in prefetch_related_objects() is "
> 2550 "deprecated. Implement get_prefetch_querysets() instead.",
> 2551 RemovedInDjango60Warning,
> 2552 stacklevel=2,
> 2553 )
>
> File ~/work_projects/saritasa-python-boilerplate-django/.venv/lib/python3.13/site-packages/django/contrib/contenttypes/fields.py:668, in create_generic_related_manager..GenericRelatedObjectManager.get_prefetch_querysets(self, instances, querysets)
> 665 object_id_converter = instances[0]._meta.pk.to_python
> 666 content_type_id_field_name = "%s_id" % self.content_type_field_name
> 667 return (
> --> 668 queryset.filter(query),
> 669 lambda relobj: (
> 670 object_id_converter(getattr(relobj, self.object_id_field_name)),
> 671 getattr(relobj, content_type_id_field_name),
> 672 ),
> 673 lambda obj: (obj.pk, self.get_content_type(obj).pk),
> 674 False,
> 675 self.prefetch_cache_name,
> 676 False,
> 677 )
>
> File ~/work_projects/saritasa-python-boilerplate-django/.venv/lib/python3.13/site-packages/django/db/models/query.py:1476, in QuerySet.filter(self, *args, **kwargs)
> 1471 """
> 1472 Return a new QuerySet instance with the args ANDed to the existing
> 1473 set.
> 1474 """
> 1475 self._not_support_combined_queries("filter")
> -> 1476 return self._filter_or_exclude(False, args, kwargs)
>
> File ~/work_projects/saritasa-python-boilerplate-django/.venv/lib/python3.13/site-packages/django/db/models/query.py:1488, in QuerySet._filter_or_exclude(self, negate, args, kwargs)
> 1486 def _filter_or_exclude(self, negate, args, kwargs):
> 1487 if (args or kwargs) and self.query.is_sliced:
> -> 1488 raise TypeError("Cannot filter a query once a slice has been taken.")
> 1489 clone = self._chain()
> 1490 if self._defer_next_filter:
>
> TypeError: Cannot filter a query once a slice has been taken.
>
> ```
Maybe slice doesnt work with generic relation?
---
### Author:  [@charettes](https://forum.djangoproject.com/u/charettes)
#### Post date: [April 18, 2025, 4:29pm UTC](https://forum.djangoproject.com/t/prefetch-top-n-most-recent-related-objects/39767/7 "2025-04-18T16:29:06Z")
Thanks for providing more details, greatly appreciated.
> Maybe slice doesn’t work with generic relation?
It’s effectively not implemented but [it’s technically feasibly to](https://github.com/django/django/compare/main...charettes:django:generic-relation-sliced-prefetch) if you’d like to request the feature to be added.
---
### Author:  [@OttoAndrey](https://forum.djangoproject.com/u/OttoAndrey)
#### Post date: [April 21, 2025, 12:52am UTC](https://forum.djangoproject.com/t/prefetch-top-n-most-recent-related-objects/39767/8 "2025-04-21T00:52:24Z")
That would be great!