Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Conditional bulk update in Django using grouping

Suppose I have a list of transactions with the following model definition:

class Transaction(models.Model):
    amount = models.FloatField()
    client = models.ForeignKey(Client)
    date = models.DateField()
    description = models.CharField()
    invoice = models.ForeignKey(Invoice, null=True)

Now I want to create invoices for each client at the end of the month. The invoice model looks like this:

class Invoice(models.Model):
    client = models.ForeignKey(Client)
    invoice_date = models.DateField()
    invoice_number = models.CharField(unique=True)

    def amount_due(self):
        return self.transaction_set.aggregate(Sum('amount'))

def create_invoices(invoice_date):
    for client in Client.objects.all():
    transactions = Transaction.objects.filter(client=client)
    if transactions.exists():
        invoice = Invoice(client=client, number=get_invoice_number(), date=invoice_date)
            invoice.save()
            transactions.update(invoice=invoice)

I know I can create all the invoices with a bulk create in 1 query with bulk create, but I would still have to the set the invoice field in the transaction model individually.

Is it possible to set the invoice field of all the Transaction models with a single query after I've created all the invoices? Preferably in using the ORM but happy to use raw SQL if required.

I know I can also use group by client on the transaction list to get the total per client, but then the individual entries are not linked to the invoice.

like image 714
Kritz Avatar asked Feb 11 '19 08:02

Kritz


2 Answers

To complement @Bernhard Vallant answer. You can use only 3 queries.

def create_invoices(invoice_date):
    # Maybe use Exists clause here instead of subquery, 
    # do some tests for your case if the query is slow
    clients_with_transactions = Client.objects.filter(
        id__in=Transaction.objects.values('client')
    )

    invoices = [
        Invoice(client=client, number=get_invoice_number(), date=invoice_date)
        for client in clients_with_transactions
    ]

    # With PostgreSQL Django can populate id's here
    invoices = Invoice.objects.bulk_create(invoices)

    # And now use a conditional update
    cases = [
        When(client_id=invoice.client_id, then=Value(invoice.pk))
        for invoice in invoices
    ]
    Transaction.objects.update(invoice_id=Case(*cases))
like image 76
Alexandr Tatarinov Avatar answered Nov 15 '22 07:11

Alexandr Tatarinov


You could try constructing a conditional update query if you are able to generate a mapping from clients to invoices before:

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

# generate this after creating the invoices
client_invoice_mapping = {
    # client: invoice
}

cases = [When(client_id=client.pk, then=Value(invoice.pk)) 
         for client, invoice in client_invoice_mapping.items()]

Transaction.objects.update(invoice_id=Case(*cases))

Note that Conditional Queries are available since Django 1.8. Otherwise you may look into constructing something similar using raw SQL.

like image 30
Bernhard Vallant Avatar answered Nov 15 '22 07:11

Bernhard Vallant