Django Rest Framework, database queries optimization

Question

I'm new at using an ORM to perform my databases calls and I have troubles finding a good way to optimize my code/queries.

I have Productss that can be in ProductGroups. Products can have 0 to n Prices. A Price is defined by its ActorLocation and its value. I would like to display the min and max of the Prices of all the Products that are in a ProductGroup.

The code I wrote is working but is extremely slow due to the amount of db access. Any help to go to the good direction will be appreciated :)

Models :

class ProductGroup(OwnedClass):
    name = models.CharField(max_length=200, blank=False)

class Product(models.Model):
    name = models.CharField(max_length=200, blank=True)
    internal_code = models.CharField(max_length=200, blank=True)
    description = models.CharField(max_length=1000, blank=True)
    product_group = models.ForeignKey(
        ProductGroup, blank=True, null=True, on_delete=models.CASCADE)

class Price(models.Model):
    price_without_taxes = models.FloatField(default=None, null=True)
    location = models.ForeignKey(
        ActorLocation, blank=True, null=True, on_delete=models.CASCADE)
    product = models.ForeignKey(Product, on_delete=models.CASCADE)

class ActorLocation(models.Model):
    name = models.CharField(max_length=200, blank=False, null=False)

Here is my function to generate the context I send to my ProductGroupSerializer :

def get_context(product_groups):
    locations = company.actor_locations.all()

    for product_group in product_groups:
        price_list = []
        products = product_group.product_set.all()

        for product in products:
            for location in locations:
                price = Price.objects.filter(product=product, location=location).last()
                    if price:
                        price_list.append(price.price_without_taxes)

        if price_list:
            context = {'lowest_product_price': round(min(price_list), 2), 'highest_product_price': round(max(price_list), 2)}
        else:
            context = {'lowest_product_price': "", 'highest_product_price': ""}

        contexts[product_group.id] = context

    return contexts

take a look at select_related() and prefetch_related() docs.djangoproject.com/en/2.0/ref/models/querysets/… . See also docs.djangoproject.com/fr/2.0/topics/db/optimization/… — jmny
– jmny, Commented May 14, 2018 at 8:18

user8060120 · Accepted Answer · 2018-05-14 07:43:23Z

3

you can try aggregates-over-a-queryset, for example:

from django.db.models import Max, Min

locations = company.actor_locations.all()
Price.objects.annotate(
    lowest_product_price=Min(price_without_taxes),
    highest_product_price=Max(price_without_taxes)
    ).filter(
    product__product_group__in=product_groups,
    location__in=locations
    ).values(
    'lowest_product_price', 'highest_product_price')

answered May 14, 2018 at 7:43

user8060120

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

2 Comments

Alk Over a year ago

Thanks for your answer, I'm wondering how to make it work on my app, I'll come back to you when it will be done

user8060120 Over a year ago

glad to help you!

Collectives™ on Stack Overflow

Django Rest Framework, database queries optimization

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest