I have a database structure which can be simplified as following (Version and AdditionalInfo not shown since not directly related to my question):
class Image(models.Model):
file = models.ImageField(blank=False, null=False, upload_to='images')
version = models.ForeignKey(Version, blank=False, null=False)
class ExampleImage(models.Model):
example = models.ForeignKey('Example', blank=False, null=False)
image = models.ForeignKey(Image, blank=False, null=False)
additional_info = models.ForeignKey(AdditionalInfo, blank=True, null=True)
class Example(models.Model):
name = models.Charfield(max_length=255)
images = models.ManyToManyField(Image, through=ExampleImage, through_fields=('example', 'image')
def get_default_image(self):
try:
image = self.images.get(version=Version.objects.get(name=DEFAULT_VERSION))
except Image.DoesNotExist:
# Get some other image, this happens maybe 1 out of 10
Now I have a very common need to query all Example objects along with their default image. Currently, I'm doing somewhat like following:
example_list = []
examples = Example.objects.all()
for example in examples:
example_dict = dict(name=example.name, image=example.get_default_image())
example_list.append(example_dict)
# Then show example_list in template
This approach works but it causes thousands of database queries and takes over one minute to perform which is little too long to download a single web page!
So my question is, what is the right approach to optimize this kind of use case. I could possibly set default_image field for the Example model (and use select_related then, but this makes adding images more complicated), hard code the Version id to get_default_image method (not so flexible), make get_default_image method to cached_property etc. but I'm not exactly sure which approach should be used. I already tried many tricks but nothing seems to help my situation. I should find a solution which allows me to query both example and default image with one big query, rather than doing the work in for loop.
Is DEFAULT_VERSION a module constant? If so, you can factor out the version=Version.objects.get(name=DEFAULT_VERSION) call so you don't have to execute it for every example in examples.
You should also be able to work with a custom Prefetch object: https://docs.djangoproject.com/en/1.11/ref/models/querysets/#django.db.models.Prefetch . Something along the lines of this (though you'll have to test and tweak yourself):
qs = Image.filter(version=Version.objects.get(name=DEFAULT_VERSION))
pref = Prefetch('images', queryset=qs, to_attr='image')
examples = Example.objects.prefetch_related(pref)
Ideally of course you'd set Example.default_image to a OneToOneField so you can retrieve the default images efficiently.
The cached_property decorator won't really help you here, because it only caches the property as long as the instance exists. It does help if you need to call instance.get_default_image() multiple times, but you'll still need to execute get_default_image once for each instance that you fetch.
Hope that helps