Merge two Querysets in django that get annotated

user_12642917 0
Viewed 190

I have a model "Shift" for guards schedule program I do annotation on two django queryset to create status column for night and day shifts

        night_shift=Shift.objects.filter(nightـguard__guards__pk=self.request.user.pk).annotate(status=Value("night", output_field=CharField()))
    day_shift=Shift.objects.filter(dayـguard__guards__pk=self.request.user.pk).annotate(status=Value("day", output_field=CharField()))

but when i merge this two queryset with code below

my_shift=(day_shift | night_shift).distinct()

The annotation field "status" that i create did not work very well and all status get to "day"

what is the solotion ?

django-views django-models django
1 Answers

You need to use Conditional Expressions (The Case and When expressions)to annotate things conditionally.

from django.db.models import Case, CharField, Q, Value, When


my_shift = Shift.objects.filter(
    Q(nightـguard__guards__pk=self.request.user.pk) | Q(dayـguard__guards__pk=self.request.user.pk)
).annotate(
    status=Case(
        When(
            nightـguard__guards__pk=self.request.user.pk,
            then=Value("night", output_field=CharField())
        ),
        When(
            dayـguard__guards__pk=self.request.user.pk,
            then=Value("day", output_field=CharField())
        ),
    ),
).distinct()
user_149918640
Related