Django Rest框架使用group by减少查询次数

Question

我正在使用Django Rest框架编写api。api获取一个客户端列表。A客户有很多项目。我的api应该返回客户的项目完成，待定和总数的数量的列表。我的api工作，但它有太多的sql查询。api被分页

class ClientViewSet(ModelViewSet):
"""
A simple view for creating clients, updating and retrieving
"""
model = Client
queryset = Client.objects.all()
serializer_class = ClientSerializer

现在我的客户端序列化器

class ClientSerializer(serializers.ModelSerializer):
total_projects_count = serializers.SerializerMethodField()
on_going_projects_count = serializers.SerializerMethodField()
completed_projects_count = serializers.SerializerMethodField()
class Meta:
model = Client
fields  = __all__
def get_total_projects_count(self, obj):
return obj.total_projects_count()
def get_on_going_projects_count(self, obj):
return obj.on_going_project_count()
def get_completed_projects_count(self, obj):
return obj.completed_projects_count()

项目有一个客户端外键。我尝试通过使用annotate来获取下面和组中的所有产品。但是annotate只能在单个字段上工作。

projects = Project.objects.filter(client__in=queryset).values('client', 'status')

如何对多个字段进行分组，并将额外的参数传递给序列化器。或者有没有更好的方法。我也尝试了prefetch_related，但total_projects_count仍在执行新的sql查询

Answer 1

您需要在查询集中注释计算的字段，然后使用注释的列返回相关的结果，而不是调用这些方法。这将确保使用单个查询检索所有数据，这肯定会更快。

1. 更新查询集

class ClientViewSet(ModelViewSet):
"""
A simple view for creating clients, updating and retrieving
"""
model = Client
queryset = Client.objects.annotate(total_projects_count_val=...)
serializer_class = ClientSerializer

2. 然后，在序列化器中，使用带注释的列

class ClientSerializer(serializers.ModelSerializer):
total_projects_count = serializers.SerializerMethodField()
on_going_projects_count = serializers.SerializerMethodField()
completed_projects_count = serializers.SerializerMethodField()
class Meta:
model = Client
fields  = __all__
def get_total_projects_count(self, obj):
return obj.total_projects_count_val
...

查看方法名，我认为您将需要Case-When注释。

Answer 2

我使用下面的查询来减少查询

from django.db.models import Count, Q
pending = Count('project', filter=Q(project__status="pending"))
finished = Count('project', filter=Q(project__status="finished"))
queryset = Client.objects.annotate(pending=pending).annotate(finished=finished)

现在可以访问queryset[0]。完成等。当我使用提供drf的分页时，生成的查询是

SELECT "clients_client"."id",
"clients_client"."created_at",
"clients_client"."updated_at",
"clients_client"."client_name",
"clients_client"."phone_number",
"clients_client"."email",
"clients_client"."address_lane",
"clients_client"."state",
"clients_client"."country",
"clients_client"."zipCode",
"clients_client"."registration_number",
"clients_client"."gst",
COUNT("projects_project"."id") FILTER (WHERE "projects_project"."status" = 'pending') AS "pending",
COUNT("projects_project"."id") FILTER (WHERE "projects_project"."status" = 'finished') AS "finished"
FROM "clients_client"
LEFT OUTER JOIN "projects_project"
ON ("clients_client"."id" = "projects_project"."client_id")
GROUP BY "clients_client"."id"
ORDER BY "clients_client"."id" ASC
LIMIT 6