如何在Django QuerySet中查询多对多字段的实例存在性

模型定义 #

class Resource(models.Model):
    name = models.CharField(max_length=100, unique=True)

class Location(models.Model):
    name = models.CharField(max_length=100, unique=True)
    resources = models.ManyToManyField(Resource)

需求 #

针对某个Resource实例，查询该实例在每个Location中的存在情况，期望返回如下格式的JSON：

[
    {
        "id": 1,
        "name": "loaction_A",
        "resource": true
    }, 
    {
        "id": 2,
        "name": "location_B",
        "resource": false
    },
    ...
]

尝试的错误代码 #

def resource_view(request, res_id):
    res = get_object_or_404(Resource, pk=res_id)
    locations = Location.objects.values('id', 'name')\
        .annotate(resource=Q(resources=res))
    return JsonResponce({'locations': list(locations)})

参考SQL逻辑 #

已知需要实现类似以下的SQL效果：

select
    app_location.*,
    1 in (
        select resource_id
        from app_location_resources
        where location_id = app_location.id
    ) as resource
from app_location

正确的Django QuerySet实现 #

方法一：使用Exists子查询（与参考SQL逻辑一致） #

from django.db.models import Exists, OuterRef
from django.http import JsonResponse
from django.shortcuts import get_object_or_404

def resource_view(request, res_id):
    res = get_object_or_404(Resource, pk=res_id)
    # 子查询：判断当前Location是否关联目标Resource
    subquery = res.location_set.filter(id=OuterRef('id'))
    locations = Location.objects.values('id', 'name')\
        .annotate(resource=Exists(subquery))
    return JsonResponse({'locations': list(locations)})

方法二：使用Case+When条件判断 #

from django.db.models import Case, When, BooleanField
from django.http import JsonResponse
from django.shortcuts import get_object_or_404

def resource_view(request, res_id):
    res = get_object_or_404(Resource, pk=res_id)
    locations = Location.objects.values('id', 'name')\
        .annotate(
            resource=Case(
                When(resources=res, then=True),
                default=False,
                output_field=BooleanField()
            )
        )
    return JsonResponse({'locations': list(locations)})

注意事项 #

• 原代码中的JsonResponce是拼写错误，正确应为JsonResponse
• 两种方法都能返回符合要求的布尔值resource字段，其中Exists子查询的性能更优，尤其是数据量较大时

内容的提问来源于stack exchange，提问作者Ezon Zhao