Advanced filtering for many-to-many annotations
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?
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