Django sub-query in Case When-CodePudding

I have such query

qty_used_annotation = Case(
        When(scope='ticket', then=BookedTicket.objects.filter(
                global_discount_code__code=F('code'), booking__status__in=['i', 'c', 'g', 'r', 's']
            ).count()),
        When(scope='booking', then=Booking.objects.filter(
                bookedticket__global_discount_code__code=F('code'),
                status__in=['i', 'c', 'g', 'r', 's']
            ).count()),
        output_field=IntegerField()
    )

And it is not working. The error is Cannot resolve keyword 'code' into field. Can somebody explain how to fix it or why it is not working. Thanks

CodePudding user response：

In subqueries you need to use OuterRef instead of F to access field from outer query:

from django.db.models import OuterRef, Subquery, Count

qty_used_annotation = Case(
        When(scope='ticket', then=Subquery(BookedTicket.objects.filter(
                global_discount_code__code=OuterRef('code'), booking__status__in=['i', 'c', 'g', 'r', 's']
            ).annotate(count=Count('id')).values('count'))),
        When(scope='booking', then=Subquery(Booking.objects.filter(
                bookedticket__global_discount_code__code=OuterRef('code'),
                status__in=['i', 'c', 'g', 'r', 's']
            ).annotate(count=Count('id')).values('count'))),
        output_field=IntegerField()
    )