Django多表链式select_related关联及用户封禁状态字段实现

问题1：实现三表链式select_related关联查询 #

你的错误源于反向关联字段名的误用：由于UserBan的user_id是ForeignKey且设置了primary_key=True，这强制每个用户只能对应一条封禁记录，Django会将GenericUserData到UserBan的反向关联识别为一对一关系，反向字段名为userban（模型名小写），而非多对一默认的userban_set。

修正后的查询集代码： #

class NormalAccountList(generics.ListAPIView):
    serializer_class = AccountNormalSerializer
    authentication_classes = [JWTAuthentication]
    permission_classes = [IsEmpReadAllUsersOrPost]

    def get_queryset(self):
        # 链式select_related同时关联userid和userid的userban字段
        queryset = NormalSellerDetails.objects.select_related('userid', 'userid__userban').all()
        return queryset

序列化器调整： #

userban属于GenericUserData的关联字段，需在GenericUserRetrievalSerializer中添加该字段（需先定义UserBanSerializer）：

# 先定义UserBanSerializer
class UserBanSerializer(serializers.ModelSerializer):
    class Meta:
        model = UserBan
        fields = ['from_date']

class GenericUserRetrievalSerializer(serializers.ModelSerializer):
    userban = UserBanSerializer(read_only=True)
    class Meta:
        model = GenericUserData    
        fields = [ "email", "first_name", 'last_name', 'is_seller', 'is_normal', 'is_emp', 'is_male', 'bdate', 'userban']

class AccountNormalSerializer(serializers.ModelSerializer):
    userid = GenericUserRetrievalSerializer(many=False)
    class Meta:
        model = NormalSellerDetails
        fields = ['mobileauthenticationenabled',
                'emailauthenticationenabled',
                'mobilephone',
                'profilepiclink',
                'userid'
                ]

调整后即可通过userid字段嵌套返回UserBan的相关数据。

问题2：新增封禁状态字段（存在记录返回True，否则False） #

推荐使用数据库层annotate实现，避免N+1查询问题：

步骤1：在查询集里添加annotate字段 #

from django.db.models import Exists, OuterRef

class NormalAccountList(generics.ListAPIView):
    serializer_class = AccountNormalSerializer
    authentication_classes = [JWTAuthentication]
    permission_classes = [IsEmpReadAllUsersOrPost]

    def get_queryset(self):
        queryset = NormalSellerDetails.objects.select_related('userid').annotate(
            # 用Exists子查询判断当前用户是否有封禁记录
            is_banned=Exists(UserBan.objects.filter(user_id=OuterRef('userid__id')))
        ).all()
        return queryset

步骤2：在序列化器中添加对应字段 #

class AccountNormalSerializer(serializers.ModelSerializer):
    userid = GenericUserRetrievalSerializer(many=False)
    # 新增只读布尔字段返回封禁状态
    is_banned = serializers.BooleanField(read_only=True)
    class Meta:
        model = NormalSellerDetails
        fields = ['mobileauthenticationenabled',
                'emailauthenticationenabled',
                'mobilephone',
                'profilepiclink',
                'userid',
                'is_banned'
                ]

若不想修改查询集，也可使用SerializerMethodField实现（性能稍差，会触发额外查询）：

class AccountNormalSerializer(serializers.ModelSerializer):
    userid = GenericUserRetrievalSerializer(many=False)
    is_banned = serializers.SerializerMethodField()

    def get_is_banned(self, obj):
        # 检查userid是否有userban关联对象
        return hasattr(obj.userid, 'userban')

    class Meta:
        model = NormalSellerDetails
        fields = ['mobileauthenticationenabled',
                'emailauthenticationenabled',
                'mobilephone',
                'profilepiclink',
                'userid',
                'is_banned'
                ]

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