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-22 22:17:44Z

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, 2016 at 22:17

answered Jan 22, 2016 at 22:08

Shang Wang

25.7k21 gold badges77 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rush Over a year ago

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: stackoverflow.com/questions/21439031/…

Shang Wang Over a year ago

Ah, yea, forgot that. raw sql is the way. I almost have to cry because it looks so close to working.

rush Over a year ago

It still helped me a lot to get the answer ;) Thx.

Shang Wang Over a year ago

OK. It's not a good answer, but at least what I said about slowness of looping is True. :)

Pooya Kamranjam · Accepted Answer · 2021-06-25 13:33:55Z

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

Collectives™ on Stack Overflow

Bulk update in django with calculations

2 Answers 2

4 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Linked

Related