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.
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))
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With