Django query optimization: reverse search on _set with filter
I have these models and I need to do some calculations and present them to the user. I am processing approximately 2-3k lines which results in 4k queries being made to the DB (shown in the debug toolbar). Is there a way to optimize this? I tried with prefetch_related but it just adds another request at the top of the 4k that gets executed.
class Cart(models.Model):
name = models.CharField(max_length=15)
user = models.OneToOneField(User)
def sum_for_this(self, taxtype, tax):
return self.carttax_set.filter(tax__type__name=taxtype,
tax__tax=tax).aggregate(
sum=Coalesce(Sum('tax_amount'), Value('0'))
).get('sum')
class TaxType(models.Model):
name = models.CharField(max_length=10)
class Tax(models.Model):
name = models.CharField(max_length=100)
type = models.ForeignKey(TaxType)
tax = models.DecimalField()
class CartTax(models.Model):
cart = models.ForeignKey(Cart)
tax = models.ForeignKey(Tax)
base = models.IntegerField()
tax_amount = models.IntegerField()
What I am doing in the template:
{% for cart in cartlist %}
{{ cart.sum_for_this }}
{% endfor %}
I tried with this but no effect:
Cart.objects.prefetch_related('carttax_set').all()
This method def sum_for_this
fulfills all requests.
+3
source to share
1 answer
Try using annotation with conditional expressions . Your request will look like this:
from django.db.models import Q, F, Sum, Case, When, IntegerField
from django.db.models.functions import Coalesce
cartlist = Cart.objects.annotate(
sum=Coalesce(Sum(Case(
When(Q(carttax__tax__type__name=taxtype) & Q(carttax__tax__tax=tax), then=F('carttax__tax_amount')),
output_field=IntegerField()
)), 0)
)
And in the template:
{% for cart in cartlist %}
{{ cart.sum }}
{% endfor %}
+3
source to share