I need help in a Django annotation.
I have a Django data model called Photo, and another called PhotoStream (one PhotoStream can have many Photos - detailed models at the end). I get the most recent 200 photos simply by: Photo.objects.order_by('-id')[:200]
To every object in the above queryset, I want to annotate the count of all related photos. A related photo is one which is (i) from the same PhotoStream, (ii) whose timestamp is less than or equal to the time stamp of the object in question.
In other words:
for obj in context["object_list"]:
count = Photo.objects.filter(which_stream=obj.which_stream).order_by('-upload_time').exclude(upload_time__gt=obj.upload_time).count()
I'm new to this, and can't seem to translate the for loop above into a queryset annotation. Any help?
Here's the photo and photostream data models with relevant fields:
class Photo(models.Model):
owner = models.ForeignKey(User)
which_stream = models.ForeignKey(PhotoStream)
image_file = models.ImageField(upload_to=upload_photo_to_location, storage=OverwriteStorage())
upload_time = models.DateTimeField(auto_now_add=True, db_index=True)
class PhotoStream(models.Model):
stream_cover = models.ForeignKey(Photo)
children_count = models.IntegerField(default=1)
creation_time = models.DateTimeField(auto_now_add=True)
So far my attempt has been:
Photo.objects.order_by('-id').annotate(num_related_photos=Count('which_stream__photo__upload_time__lte=F('upload_time')))[:200]
Gives an invalid syntax error.
Whereas the following works, but doesn't cater to my timestamp related requirement in (ii) above:
Photo.objects.order_by('-id').annotate(num_related_photos=Count('which_stream__photo'))[:200]
