CODEWITHSUNDEEP
pythonsql-serverdjangodjango-models
Shubham Periwal
Shubham Periwal

Reputation: 2248

Efficiently update same field multiple objects django

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"

Upvotes: 4

Views: 2060

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476709

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,
)

Upvotes: 4

Related Questions