how to query filter self foreign key-CodePudding

    query = Services.objects.filter(parent_id__isnull=True,sub_service__type=0)

When im filtering sub_service__type=1 It returns correct output. i.e. type 1 for sub_service, But when i change it to sub_service__type=0 filters doesnot work. Instead it returns me every output. i.e. type 0,1 Instead of type 0

Heres Code:

# MODELS

class Services(models.Model):
    type_ = ((1, 'INCLUSIVE'),
             (0, 'EXCLUSIVE'))
    service_id = models.AutoField(primary_key=True)
    parent_id = models.ForeignKey('self', on_delete=models.SET_NULL, null=True, blank=True, related_name='sub_service')
    type = models.SmallIntegerField(blank=True, null=True, choices=type_)


# VIEWS

@action(detail=False, methods=['get'], permission_classes=(AllowAny,))
def service_list(self, request):

    query = Services.objects.filter(parent_id__isnull=True,sub_service__type=0)
    serializer = ServiceSerializer(query , many=True).data
    return Response(serializer)


# SERIALIZER

class SubServiceSerializer(serializers.ModelSerializer):

    class Meta:
        model = Services
        fields = "__all__"


class ServiceSerializer(serializers.ModelSerializer):
    sub_service = SubServiceSerializer(many=True)

    class Meta:
        model = Services
        fields = "__all__"

CodePudding user response：

If you filter with sub_service__type=1 you retrieve all Services that have at least one related Service with type=1. But it is thus allowed that there are other related sub-Services with a different type. The .sub_service manager will furthermore not filter the queryset of related objects.

You can make use of a Prefetch object [Django-doc] to filter the relation as well:

from django.db.models import Prefetch

query = Services.objects.filter(
    parent_id__isnull=True,sub_service__type=0
).prefetch_related(Prefetch('sub_service', Service.objects.filter(type=0)))