What is the best method to query db in django rest framework

Question

I have models.py

class Category(MPTTModel):
    # few fields

class Brand(models.Model):
    # few fields

class Attribute(models.Model):
   # few fields

class AttributeValue(models.Model):
   attributes = models.ForeignKey(Attribute, # other conditions)
   # few fields

class Product(models.Model):
   category = models.ForeignKey(Category, # other conditions)
   brand = models.ForeignKey(Brand, # other conditions)
   attributes = models.ManyToManyField(Attribute, # other conditions)
   # few other fields

class ProductImages(models.Model):
   product = models.ForeignKey(Product, # other conditions)

In views.py, I have

class ProductAPIView(generics.GenericAPIView):

    serializer_class = ProductSerializer
    queryset = Product.objects.all()

serializers.py

class ProductSerializer(serializers.ModelSerializer):
    brand = serializers.SlugRelatedField(queryset = Brand.objects.all())
    category = serializers.SlugRelatedField(queryset = Category.objects.all())
    attributes = AttributeSerializer(many = True, read_only = True)
    product_images = ProductImageSerializer(many = True, read_only = True)

I'm getting my json response like this

{
    "brand": ...,
    "category": ...,
    "attributes": [
        { "attribute_values": [...] },
        { "attribute_values": [...] }
    ],
    "product_images": [{...}, {...}]
}

I came across select_related and prefetch_related which will optimise the db queries on foreign key fields and many-to-many fields.

I changed the queryset to queryset = Product.objects.select_related('category', 'brand').all()

How can i change the queryset in ProductAPIView to include the attributes, product_images and attribute_values fields also and improve the performance here ?

score 1 · Answer 1 · answered May 13 '23 at 11:10

for the first part of your question you can take a look at Prefetch and this question.

for debugging purposes I always use this decorator:

from django.db import connection, reset_queries
import time
import functools


def query_debugger(func):

    @functools.wraps(func)
    def inner_func(*args, **kwargs):

        reset_queries()
        
        start_queries = len(connection.queries)

        start = time.perf_counter()
        result = func(*args, **kwargs)
        end = time.perf_counter()

        end_queries = len(connection.queries)

        print(f"Function : {func.__name__}")
        print(connection.queries)
        print(f"Number of Queries : {end_queries - start_queries}")
        print(f"Finished in : {(end - start):.2f}s")
        return result

    return inner_func

And use it like this:

@query_debugger
def get(self, request, *args, **kwargs):
   pass

score 1 · Accepted Answer · answered May 13 '23 at 23:54

1

you can chain select_realted & prefetch_related

class ProductAPIView(generics.GenericAPIView):

    serializer_class = ProductSerializer
    queryset = Product.objects.select_related('category', 'brand').prefetch_related("attributes")

or override get_queryset function

class ProductAPIView(generics.GenericAPIView):

    serializer_class = ProductSerializer
    
    def get_queryset(self):
        qs =  super().get_queryset()
        return qs.select_related('category', 'brand').prefetch_related("attributes")

You can use the Prefetch class to specify the queryset that is used in prefetch_related() and this way combine it with select_related():

answered May 13 '23 at 23:54

Yusuf Adel

50
5

Hi, I've added the `attributes` but i see that for `product_images` and `attribute_values`, separate queries are going. Can i optimize more here ? – Srivatsa May 14 '23 at 04:47
`prefetch_related("attributes__attribute_value", "product_images")` – Yusuf Adel May 14 '23 at 11:53
working with a reverse FK we include such fields in `prefetch_related` method, for the first parameter `attributes__attribute_value` it will prefect all related object of `attributes` as well their reverse FK to `AttributeValue` class, counted as 3 joins to product table in total – Yusuf Adel May 14 '23 at 11:55

What is the best method to query db in django rest framework

2 Answers2