Optimize Django Query in loop with branch

Question

I'm working on a Django project, on a certain view I want to retrieve all the payment orders for all clients, and its payment.

The main query is this one:

orders = PaymentOrder.objects.filter(
    created_date__gte=init_date, created_date__lte=end_date
).values(
    'invoice_sat__comp_folio', 'created_date', 'client__name',
    'client__last_name', 'total', 'status', 'pk', 'client__pk'
).order_by('-created_date')

Also, I want the paid amount for each Order, and I'm doing this:

for order in orders:
    order_payments = Payment.objects.filter(
        order__pk=order['pk']
    ).values('amount').aggregate(total=Sum('amount'))
    if order_payments['total'] is not None:
        to_pay = order['total'] - order_payments['total']
    else:
        to_pay = order['total']
    order.update(to_pay=to_pay)

The problem here is, the template takes a very long time to load, I have something like 15,000 results. Is there any way to optimize the query, or, to update the extra info (amount to pay) of the orders.

Thanks a lot!

MODELS UPDATE:

class PaymentOrder(models.Model):
    STATUS = (
        (0, "Por Pagar"),
        (1, "Pagado"),
    )
    client = models.ForeignKey(Client, verbose_name='Cliente')
    invoice_sat = models.ForeignKey(InvoiceSAT, blank=True, null=True)
    invoice_period = models.DateField(verbose_name="Periodo de     Facturación")
    created_date = models.DateTimeField(verbose_name='Fecha de Creacion',
                                    default=datetime.datetime.now(),
                                    db_index=True)
    cancelled_date = models.DateField(verbose_name='Fecha de Cancelacion',
                                  default=None, null=True, blank=True)
    due_date = models.DateField(verbose_name="Fecha de Vencimiento")
    status = models.IntegerField(max_length=1, choices=STATUS, default=0)
    subtotal = models.DecimalField(max_digits=10, decimal_places=2,
                               verbose_name='Subtotal', default=0)
    total = models.DecimalField(max_digits=10, decimal_places=2,
                            verbose_name="Cantidad", default=0)

class Payment(models.Model):
    order = models.ForeignKey(PaymentOrder, verbose_name='Orden de Pago')
    payment_date = models.DateField(verbose_name="Fecha de Pago")
    amount = models.DecimalField(max_digits=10, decimal_places=2,
                             verbose_name="Cantidad a pagar")

Post your models. The solution is going to be (1.) try to make it a queryset update rather than looping through orders in Python-land, and (2.) Try to pull everything in with one query rather than hitting the database again in a loop (`Payment.objects.filter....` etc.). But there's not enough info here to tell if that's really possible, much less how you could/should do it. — Two-Bit Alchemist, Jul 01 '15 at 18:14

NightShadeQueen · Answer 1 · 2015-07-01T18:43:59.713

0

select_related might be what you want, which would let you avoid hitting the db for each iteration in that for loop.

orders = PaymentOrder.objects.filter(
    created_date__gte=init_date, created_date__lte=end_date
).order_by('-created_date').select_related('payment') # one giant hit to the db
for order in orders:
    order_payments = order.payment_set.values('amount').aggregate(total=Sum('amount'))
        #uses data already pulled from select_related so does not hit db

Documentation here: https://docs.djangoproject.com/en/1.8/ref/models/querysets/#django.db.models.query.QuerySet.select_related

edited Jul 01 '15 at 18:43

answered Jul 01 '15 at 18:18

NightShadeQueen

3,284
3
24
37

I thinks this would not work, because the relations on my models. The PaymentOrder models dont have the Payment relation. :/ – Jesús Cota Jul 01 '15 at 18:33
Which is why it's payment_set :P Django lets you look up foreignkeys in reverse http://stackoverflow.com/questions/15306897/django-reverse-lookup-of-foreign-keys @jesuscc29 – NightShadeQueen Jul 01 '15 at 18:35
Ohhh, i get it. But, i cant access to "payment_set", because "order" is actually a dict() and i get the ''dict' object has no attribute 'payment_set'' error. :( – Jesús Cota Jul 01 '15 at 18:41
Right. You'd have to not use the call to `.values()` in order to do this. – NightShadeQueen Jul 01 '15 at 18:46
Yes, but that is going to slow more the queries.. :/ – Jesús Cota Jul 01 '15 at 21:04
Eh, I'd time it both ways if I were you. Okay, so values is faster for looking up fewer columns, and `select_related` is faster for not having a query in a for loop (which is almost always pretty bloody slow). Or maybe having a `select_related("clients", "payments")`. Overall, I don't think your PaymentOrder class has *that* many columns anyways, and for most things I've worked on, `select_related` and `prefetch_related` ended up being faster. – NightShadeQueen Jul 01 '15 at 21:18

Optimize Django Query in loop with branch

1 Answers1