[Django]-Improve django queryset performance

by

-1👍

Try this refactored queryset.

from django.db.models import Exists, OuterRef, Subquery, When, Case, F, Q, Value
from django.db.models.functions import Coalesce

active_vouchers = Voucher.objects.filter(
    voucher_kind=VoucherKindChoices.Static_based,
    voucher_ranges__packs__product__id=OuterRef('pk'),
    voucher_ranges__packs__min_basket_value=0,
).distinct()

discount_subquery = active_vouchers.filter(
    voucher_ranges__packs__product__id=OuterRef('pk')
).values('voucher_type', 'voucher_kind')[:1]

qs = self.annotate(
    is_discounted=Exists(discount_subquery),
    voucher_type=Subquery(discount_subquery.values('voucher_type')),
    voucher_kind=Subquery(discount_subquery.values('voucher_kind')),
    voucher_fixed_amount=Coalesce(
        Subquery(
            Voucher.bll.convert_voucher_fixed_price_currency(
                active_vouchers, currency.code
            ).values('converted_fixed_price')[:1]
        ), Value(0)
    ),
    voucher_percent_amount=Subquery(
        active_vouchers.values('value_percentage_based')[:1]
    ),
    fixed_static_discount=Case(
        When(
            Q(is_discounted=True, voucher_kind=VoucherKindChoices.Static_based, voucher_type=VoucherTypeChoices.Fixed_price_based),
            then=F('voucher_fixed_amount')
        ),
        default=Value(0),
        output_field=MoneyCurrencyOutput(currency)
    ),
    percentage_static_discount=Case(
        When(
            Q(is_discounted=True, voucher_kind=VoucherKindChoices.Static_based, voucher_type=VoucherTypeChoices.Percentage_based),
            then=F('voucher_percent_amount')
        ),
        default=Value(0),
    ),
    price_after_static_discount=Case(
        When(
            Q(is_discounted=True, voucher_kind=VoucherKindChoices.Static_based, voucher_type=VoucherTypeChoices.Fixed_price_based),
            then=F('default_pack_price') - F('fixed_static_discount')
        ),
        When(
            Q(is_discounted=True, voucher_kind=VoucherKindChoices.Static_based, voucher_type=VoucherTypeChoices.Percentage_based),
            then=F('default_pack_price') * (100 - F('percentage_static_discount')) / 100
        ),
        default=Value(0),
        output_field=MoneyCurrencyOutput(currency)
    ),
)

return qs
👤bairavand

Leave a comment