Efficiently update same field multiple objects django

Question

I have a model and I want to send increment a field value by 1 for multiple records. I searched for efficient update answers like Efficient way to update multiple fields of Django model object but they assume I have the value whereas I want to increment so the value might be different for different records.

My Model looks something like this:

class Signal(models.Model):
    name = models.CharField(max_length=80)
    version = models.IntegerField()

In my view, I want to send a list of names and then update all of their versions by 1.

My current method is to look through the names, create objects, update versions, and save manually so like this:

    signals = Signal.objects.filter(name__in=signal_names)
    for signal in signals:
        signal.pk=None
        signal.version = signal.version+1
        signal.save()

This results in way too many queries and is very slow because I'll be sending 100s of names at once.

Is there a better way to do this?

NOTE: This might seem similar to bulk update questions but in this case I want to increment 1 so I dont have the value to update with. That was my struggle with existing answers.

Example:

signal table

name | version
n1 | 1
n2 | 2

I send ["n1", "n2"] in my post and the output should be

signal table

name | version
n1 | 1
n2 | 2
n1 | 2
n2 | 3

UPDATE One more contingency I found out is that the next time I want to update, I just want to update the latest version -- not all of them

So if I run the POST request again, the value should be

signal table

name | version
n1 | 1
n2 | 2
n1 | 2
n2 | 3

n1 | 3
n2 | 4

How can I filter to update the latest version for each name only? The database I'm using is "sql server"

Willem Van Onsem · Accepted Answer · 2021-08-30T08:16:00.173

3

You can make a list of new signals and create these at the database in bulk with .bulk_create(…) [Django-doc]:

# create new Signal objects

results = []
signals = Signal.objects.filter(name__in=signal_names)

for signal in signals:
    signal.pk = None
    signal.version += 1
    results.append(signal)

Signal.objects.bulk_create(results)

This will however make duplicates. If you want to update the existing Signals, then you work with .update(…) [Django-doc]:

# updating the existing the Signal objects

signals = Signal.objects.filter(name__in=signal_names).update(
    version=F('version') + 1
)

If you only want to fetch the latest version, you should alter the signals queryset with an Exists subquery [Django-doc]:

from django.db.models import Exists, OuterRef

Signal.objects.filter(
    ~Exists(Signal.objects.filter(
        name=OuterRef('name'), version__gt=OuterRef('version')
    )),
    name__in=signal_names,
)

edited Aug 30 '21 at 08:16

answered Aug 30 '21 at 07:58

Willem Van Onsem

443,496
30
428
555

Thank you for your prompt answer. This reduces my number of queries but is there a way to avoid looping completely and a cleaner code? Perhaps some inherent django function can help me? – Shubham Periwal Aug 30 '21 at 07:59
@ShubhamPeriwal: not when you create *new* objects. If you want to *update* the objects, then you can work with `.update(...)`. The looping however does not make any queries, so this will usually run with ~100k iterations per second, which is likely sufficient. After all, the database will do some looping as well to update records, so it will not alter the time complexity. – Willem Van Onsem Aug 30 '21 at 08:01
ahh ok ok noted, thanks. I dont want to update the existing rows though. I need to create a new row for each record. I guess looping is the only way! Let me try it out – Shubham Periwal Aug 30 '21 at 08:03
Willem Van Onsem ~ sorry I added one more functionality required. Is it possible to update your code to use that? I wasn't able to implement it on my end. – Shubham Periwal Aug 30 '21 at 08:12
@ShubhamPeriwal: you should alter the filter condition of the `Signal` such that you only retrieve the ones with the latest version, see edit (at the bottom of the answer). – Willem Van Onsem Aug 30 '21 at 08:16
1

@ShubhamPeriwal it feels to me your modelling is bad. If you want to do some kind of versioning an extra model with a foreign key to this one would be better. – Abdul Aziz Barkat Aug 30 '21 at 08:18

Efficiently update same field multiple objects django

1 Answers1