Update with condition in Django

Question

I have a list of id and sort:

ids = ['123', '456', '789', '901'];
sorts = [1, 3, 2, 4];

I want to update the order of each id in Django. Here is the SQL and Django i have tried:

SQL:

UPDATE  "Blogs" 
SET     sort = CASE 
                     WHEN  id = '123' THEN 1
                     WHEN  id = '456' THEN 3
                     WHEN  id = '789' THEN 2
                     WHEN  id = '901' THEN 4
                END
WHERE   id IN (ids);

Django:

Blogs.objects.filter(
            id=ids
        ).update(
            sort=Case(
                When(id=123, then=1),
                When(id=456, then=3),
                When(id=789, then=2),
                When(id=901, then=4),
            )
        )

But problem is when ids have many elements I can not specific id and then in When(id={id}, then={sort}). How can I optimize the update query in Django version. Thanks in advance.

which db you are using? sqlite3 or mysql?

zhiqiang huang
– zhiqiang huang

2017-04-10 02:03:08 +00:00
Commented Apr 10, 2017 at 2:03 — zhiqiang huang
– zhiqiang huang, Commented Apr 10, 2017 at 2:03
@zhiqianghuang I am using postgresql.

javimuu
– javimuu

2017-04-10 02:29:15 +00:00
Commented Apr 10, 2017 at 2:29 — javimuu
– javimuu, Commented Apr 10, 2017 at 2:29

AKS · Accepted Answer · 2017-04-10 04:53:11Z

Django doesn't explicitly provide the functionality you need here. You have already tried it using CASE, WHEN syntax and it would become unmanageable as the number of ids increase.

One solution that I can think of is that you create a temporary table with just (id, sort) column and join on that to update your Blog table.

Here is how you can do this:

Create a temporary table from Blog table using id and sort columns:

from django.db import connection

cursor = connection.cursor()
blog_tbl_name = Blog._meta.db_table
temp_tbl_name = "tmp_{}".format(blog_tbl_name)
sql = (
     "CREATE TEMP TABLE {temp_tbl_name} AS "
     "SELECT id, sort FROM {blog_tbl_name} LIMIT 0;"
     .format(
         temp_tbl_name=temp_tbl_name,
         blog_tbl_name=blog_tbl_name
     )
)

cursor.execute(sql)

Now, insert the (id, sort) values into this temp table:

ids = ['123', '456', '789', '901'];
sorts = [1, 3, 2, 4];

insert_sql = (
    "INSERT INTO {temp_tbl_name} (id, sort) VALUES (%s, %s);"
    .format(temp_tbl_name=temp_tbl_name)
)

# zip these two together and insert
cursor.executemany(insert_sql, zip(ids, sorts))

Now, the last part where you update the Blog table by joining ids in the temp table:

update_sql = (
    "UPDATE {blog_tbl_name} blog_tbl "
    "SET sort=tmp_tbl.sort "
    "FROM {temp_tbl_name} tmp_tbl "
    "WHERE blog_tbl.id = tmp_tbl.id;"
    .format(
         temp_tbl_name=temp_tbl_name,
         blog_tbl_name=blog_tbl_name
    )
)

cursor.execute(update_sql)

What is important to note here that this whole process requires only three queries and is manageable and efficient.

Collectives™ on Stack Overflow

Update with condition in Django

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related