Optimizing Django ORM query from multiple DB look-ups to possibly one DB look-up

Question

In a Django-based social website I maintain, users post photos. Each posted photo is a part of one photostream (i.e. a list of related photos). I calculate the 200 most recent photos via the get_queryset method of a CBV (ListView):

def get_queryset(self):
    return Photo.objects.order_by('-id')[:200]

Next, for each photo, I append the count of the number of related photos exist. That I do by first checking which photostream each photo belongs to, then getting other photos from the said stream, and lastly excluding some based on freshness. 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()

The count is then paired with each obj so that I end up with a dictionary to use in populating the template. As you would have guessed, I basically use this info to show the count of related photos alongwith each listed photo.

But doing it this way is just too many DB lookups! How can I optimize this, for performance? Please advise!

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)

AKS · Accepted Answer · 2016-04-23 11:26:51Z

1

Plesae check if you could use Conditional Aggregations like this:

from django.db.models import Count, Case, When, IntegerField

Photo.objects.annotate(
    count=Count(Case(
        When(which_stream__photo__upload_time__lte=F('upload_time')), then=1),
        output_field=IntegerField(),
    ))
).order_by('-id')[:200]

I haven't tested this but I think you will get an idea how to use it.

answered Apr 23, 2016 at 11:26

AKS

20k3 gold badges47 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Optimizing Django ORM query from multiple DB look-ups to possibly one DB look-up

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related