CODEWITHSUNDEEP
pythonsqldjango
Byteme
Byteme

Reputation: 629

Update all models at once in Django

I am trying to update position field for all objects in specific order at once in Django (python).

This is how I've done it now, but the problem is that it makes loads of queries.

    servers = frontend_models.Server.objects.all().order_by('-vote_count')

    i = 1
    for server in servers:
        server.last_rank = i
        server.save()
        i += 1

Is there a way to update with

Model.objects.all().order_by('some_field').update(position=some_number_that_changes_for each_object)

Thank you!

Upvotes: 21

Views: 19355

Answers (2)

Gareth Rees
Gareth Rees

Reputation: 65854

As far as I know, Django's object-relational mapping system doesn't provide a way to express this update operation. But if you know how to express it in SQL, then you can run it via a custom SQL query:

from django.db import connection
cursor = connection.cursor()
cursor.execute('''UPDATE myapp_server ...''')

Different database engines express this operation in different ways. In MySQL you'd run this query:

SET @rownum=0;
UPDATE myapp_server A,
       (SELECT id, @rownum := @rownum + 1 AS rank
        FROM myapp_server
        ORDER BY vote_count DESCENDING) B
SET A.rank = B.rank
WHERE A.id = B.id

In PostgreSQL I think you'd use 

UPDATE myapp_server A,
       (SELECT id, rownumber() AS rank
        OVER (ORDER BY vote_count DESCENDING)
        FROM myapp_server) B
SET A.rank = B.rank
WHERE A.id = B.id

(but that's untested, so beware!).

Upvotes: 0

lprsd
lprsd

Reputation: 87095

You can use the F() expression from django.db.models to do the same:

Model.objects.all().order_by('some_field').update(position=F(some_field)+1)

which will generate a single SQL query to update all of the column values, so it is efficient to the database too.

Upvotes: 19

Related Questions