How to Use Django Queryset update() function with foreign key field cited by F()?

user_11327073 0
Viewed 267

I want to make a simple query:

with transaction.atomic():
    Account.objects.select_for_update().filter(payments__status='PENDING', payments__created_date__lt=timezone.now()-timedelta(days=14)).update(balance=F('balance')+Sum(F('payments__amount') - F('payments__payment_fee__fee_amount')), payments__status='COMPLETED')

It turned out it wasn't simple at all. I've tried Subquery but alternatively get "Joined field references are not permitted in this query" or "FOR UPDATE is not allowed with GROUP BY clause"

Subquery doesn't work because aggregation doesn't work with select_for_update() locks. F() doesn't work because it is referencing foreign key fields...

What's the best way to make this query work? Any idea?

  • I am using postgresql behind the ORM.
postgresql django-orm django-models django
1 Answers

Here is how I did:

with transaction.atomic():
    payments_due_payout = models.Payment.objects.filter(status='PENDING', created_date__lt=timezone.now()-timedelta(days=15))
    total_payout = Subquery(models.Account.objects.filter(payments__in=payments_due_payout, id=OuterRef('id')).annotate(total_payout=Sum(F('payments__amount') - F('payments__payment_fee__fee_amount'))).values('total_payout')[:1])
    models.Account.objects.select_for_update().filter(payments__in=payments_due_payout).update(balance=F('balance')+total_payout)
    payments_due_payout.update(status='COMPLETED')

Referencing foreign key fields with F in the update() function can be solved with Subquery. But you cannot aggregate the rows locked by select_for_update().

I chose to rely on "payments_due_payout" which is not possible to modify from the client side from timezone.now()-timedelta(days=14), and I fetch the Payment created_date__lt=timezone.now()-timedelta(days=15), which is one day difference. I don't think any concurrent request for payments_due_payout will last on the Django server for 1-day-long.

So there should be no problem, but in an ideal world, we should completely prevent the possibility of the data alteration of status='PENDING', programmatically.

If you have any better idea, please let me know.

user_113270730
Related