Bulk update in django with calculations

Question

I have 2 models in my project:

class Currency(models.Model):
    title = models.CharField(max_length=100, unique=True)
    value = models.FloatField()

class Good(models.Model):
    name = models.CharField(max_length=100)
    slug = SlugField(max_length=100, unique=True)
    cost_to_display = models.IntegerField(default=0)
    cost_in_currency = models.IntegerField()
    currency = models.ForeignKey(Currency)

The idea of such model is to speed up the search by price and have all goods in one currency. Therefore I need some hook which will update all Goods in case exchange rate was updated.

In raw sql it will looks like this

mysql> update core_good set cost_to_display = cost_in_currency * (select core_currency.value from core_currency where core_currency.id = currency_id ) ;
Query OK, 663 rows affected (0.10 sec)
Rows matched: 7847  Changed: 663  Warnings: 0

Works pretty fast. Though I tried to implement the same in django admin like this (using bulk-update):

def save_model(self, request, obj, form, change):
    """Update rate values"""
    goods = Good.objects.all()
    for good in goods:
        good.cost_to_display = good.cost_in_currency * good.currency.value
    bulk_update(goods)
    obj.save()

It takes up to 20 minutes to update all records via django admin this way.

What I am doing wrong? What is the right way to update all the prices?

Shang Wang · Accepted Answer · 2016-01-22T22:17:44.263

1

This is purely untested, but it's sort of work in my mind:

from django.db.models import F
Good.objects.all().update(cost_to_display=F('cost_in_currenty') * F('currency__value'))

Even you are calling bulk_update, you still looped through all goods, which is why your process is slow.

Edit:

This won't work because F() doesn't support joined fields. It can be done using raw query.

edited Jan 22 '16 at 22:17

answered Jan 22 '16 at 22:08

Shang Wang

24,909
20
73
94

Thank you for your reply. Unfortunately it doesn't work this way. `F` doesn't allow joined field. It looks like the only solution in this case is raw sql. There is similar case: http://stackoverflow.com/questions/21439031/django-f-expressions-joined-field – rush Jan 22 '16 at 22:15
Ah, yea, forgot that. raw sql is the way. I almost have to cry because it looks so close to working. – Shang Wang Jan 22 '16 at 22:16
It still helped me a lot to get the answer ;) Thx. – rush Jan 22 '16 at 22:18
OK. It's not a good answer, but at least what I said about slowness of looping is `True`. :) – Shang Wang Jan 22 '16 at 22:19

score 1 · Answer 2 · answered Jun 25 '21 at 13:33

For the future readers: any call to good.currency in your code is hitting the database. Consider using select_related to fetch Currency and Good objects in one query:

goods = Good.objects.select_related('currency')

Also now Django comes with bulk_update method since version 2.2 docs

Bulk update in django with calculations

2 Answers2