Advanced filtering for many-to-many annotations

Question

I have following models:

class CloudObjects(models.Model):
    object_id = models.AutoField(primary_key=True)
    object_name = models.CharField(max_length=256)
    creation_time = models.DateTimeField()
    removed_date = models.DateTimeField(blank=True, null=True)
    item = models.ManyToManyField(BackupItems, db_table='cloud_object_items')

class BackupItems(models.Model):
    name = models.CharField(max_length=100)

I'd like to annotate for each BackupItem, the most recent creation_time field from CloudObject for items which are planned to be removed in removed_date in future. As an example: CloudObject looks likes this.

object_id | object_name | creation_time | removed_date | item
1 | object_one_in_cloud | 2021-01-01 | 2021-10-01 | 1
2 | object_two_in_cloud | 2021-02-02 | 2099-12-31 | 1
3 | object_three_in_cloud | 2021-03-03 | 2099-12-31 | 1
4 | object_four_in_cloud | 2021-12-31 | 2022-01-01 | 1

For above example I'd like to annotate with item 3, as it has removed_date in the future and this is the most fresh item (item 2 is also planned to be removed in future, but 3 is more recent)

Now in my Views I'd like to annotate this. I tried different ways, but can move forward now. This is last one I tried:

from django.db.models import Subquery, OuterRef

class BackupListView(ListView):
    template_name = 'somefile.html'

    def get_queryset(self):
        last_item = CloudObjects.objects.filter(item=OuterRef("pk")).filter(removed_date__gte=timezone.now()).last()
        all_items = BackupItems.objects.annotate(last_backup=Subquery(last_item.get('creation_time')))
        return all_items

How to get it working?

Answer 1

As described in the docs:

(Using get() instead of a slice would fail because the OuterRef cannot be resolved until the queryset is used within a Subquery.)

last behaves similarly with get where it tries to resolve the queryset but OuterRef needs to be in a subquery first. That's why it wouldn't work. So slicing should be used instead like so:

    def get_queryset(self):
        cloud_objects = CloudObjects.objects.filter(
            item=OuterRef("pk")
        ).filter(
            removed_date__gte=timezone.now()
        ).order_by(
            "-creation_time"
        )

        all_items = BackupItems.objects.annotate(
            last_backup_date=Subquery(
                cloud_objects.values("creation_time")[:1]
            )
        )
        return all_items