The Django REST Framework does more than just help provide reST-style interfaces in Django applications; Serialization output of interfaces; Authority management, etc.; However, using the default for everything during serialization can result in multiple queries to the database during serialization, causing performance problems (source: sample):

  • model:
from django.db import models


class Category(models.Model):
    name = models.CharField(max_length=100)


class Blogpost(models.Model):
    title = models.CharField(max_length=100)
    categories = models.ManyToManyField(Category)
Copy the code
  • url + view:
# urls.py
from rest_framework import routers
from . import views


router = routers.DefaultRouter()
router.register(r'blogposts', views.BlogpostViewSet)
---------------------------------------------------------
# views.py
from rest_framework import viewsets

class BlogpostViewSet(viewsets.ModelViewSet):
    queryset = Blogpost.objects.all().order_by('date')
    serializer_class = serializers.BlogpostSerializer
Copy the code

When this is done, call the interface, and we print our SQL on the console, we should see something like this:

SQL output Settings
LOGGING = {
    ...
    "loggers": {..."django.db.backends": {
            "handlers": ["console"]."level": "DEBUG"."propagate": False,}}}Copy the code
-- SQL output results
SELECT "app_blogpost"."id"."app_blogpost"."title" FROM "app_blogpost";

SELECT "app_category"."id"."app_category"."name" FROM "app_category" INNER JOIN "app_blogpost_categories" ON ("app_category"."id" = "app_blogpost_categories"."category_id") WHERE "app_blogpost_categories"."blogpost_id" = 1025;
SELECT "app_category"."id"."app_category"."name" FROM "app_category" INNER JOIN "app_blogpost_categories" ON ("app_category"."id" = "app_blogpost_categories"."category_id") WHERE "app_blogpost_categories"."blogpost_id" = 193;
SELECT "app_category"."id"."app_category"."name" FROM "app_category" INNER JOIN "app_blogpost_categories" ON ("app_category"."id" = "app_blogpost_categories"."category_id") WHERE "app_blogpost_categories"."blogpost_id" = 757;
SELECT "app_category"."id"."app_category"."name" FROM "app_category" INNER JOIN "app_blogpost_categories" ON ("app_category"."id" = "app_blogpost_categories"."category_id") WHERE "app_blogpost_categories"."blogpost_id" = 853;
SELECT "app_category"."id"."app_category"."name" FROM "app_category" INNER JOIN "app_blogpost_categories" ON ("app_category"."id" = "app_blogpost_categories"."category_id") WHERE "app_blogpost_categories"."blogpost_id" = 1116;
SELECT "app_category"."id"."app_category"."name" FROM "app_category" INNER JOIN "app_blogpost_categories" ON ("app_category"."id" = "app_blogpost_categories"."category_id") WHERE "app_blogpost_categories"."blogpost_id" = 1126;
SELECT "app_category"."id"."app_category"."name" FROM "app_category" INNER JOIN "app_blogpost_categories" ON ("app_category"."id" = "app_blogpost_categories"."category_id") WHERE "app_blogpost_categories"."blogpost_id" = 964;
SELECT "app_category"."id"."app_category"."name" FROM "app_category" INNER JOIN "app_blogpost_categories" ON ("app_category"."id" = "app_blogpost_categories"."category_id") WHERE "app_blogpost_categories"."blogpost_id" = 591;
SELECT "app_category"."id"."app_category"."name" FROM "app_category" INNER JOIN "app_blogpost_categories" ON ("app_category"."id" = "app_blogpost_categories"."category_id") WHERE "app_blogpost_categories"."blogpost_id" = 1112;
SELECT "app_category"."id"."app_category"."name" FROM "app_category" INNER JOIN "app_blogpost_categories" ON ("app_category"."id" = "app_blogpost_categories"."category_id") WHERE "app_blogpost_categories"."blogpost_id" = 1034; .Copy the code

Did you get a fright? In serialization, the native way is to take them out one by one and then look up the corresponding associated objects. There are four or five foreign key associated objects in my business table, and several screens of SQL are displayed after clicking that interface. If there is a large amount of data, this is definitely a great strain on the database! Later, I went to the Internet to find a more suitable solution. Let’s share the following: 1. A field that needs to be used in a one-to-many object in serialization:

# views.py
queryset = BusinessTable.objects.all().select_related(
        'one_to_one_field_1'.'one_to_one_field_2',...). ---------------------# serializer
class xxxSerializer(serializers.ModelSerializer):
    target_field = serializers.SerializerMethodField()

    def get_target_field(self, obj):.return obj.one_to_one_field_1.target_field
Copy the code

2. Serialization needs to use fields in one-to-many, many-to-many objects

queryset = BusinessTable.objects.all().prefetch_related(
        'many_to_many_field_1'.'many_to_many_field_2',...). ---------------------# serializer
class xxxSerializer(serializers.ModelSerializer):
    target_field_set = serializers.SerializerMethodField()

    def get_target_field(self, obj):.return [it.arget_field for it in obj.many_to_many_field_1.all()]
Copy the code

3. Aggregation of many-to-many fields is required in serialization

queryset = BusinessTable.objects.all().annotate(
        cnt_xxx=Count("many_to_many_field") -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -# serializer
class xxxSerializer(serializers.ModelSerializer):
    target_field = serializers.SerializerMethodField()

    def get_target_field(self, obj):
        return obj.cnt_xxx
Copy the code

4. To summarize

queryset = BlessingBlog.objects.all().annotate(
        cnt_xxx=Count("xxx") Annotate explains the statement in annotate, so Django will actually perform federated queries in external statements and then group them, rather than subquerying them directly. If you want to aggregate a subquery, you can use RowSql or extra!
    ).select_related( # one-to-one associative table data
        'yyy1'.'yyy2',
    ).prefetch_related( # one-to-many, many-to-many association table data
        'xxx1'.'xxx3',
    ).order_by('zzz1'.'zzz2')
Copy the code

Is presented in this paper, system in the end, if you use the annotate of the query to the processing of polymerization field is not achieved by the subquery, through the joint query group again, this is has a great influence on performance, and aggregating multiple fields also notice to heavy, if the performance requirements of high, can consider their handwritten query statement.

Reference: 1 Stack Overflow 2 Blog 3 Djangos website 4 Blog