Skip to content

Instantly share code, notes, and snippets.

@Skyross
Last active November 7, 2019 09:01
Show Gist options
  • Save Skyross/c150d9a251a2b3442291d84de8d0af64 to your computer and use it in GitHub Desktop.
Save Skyross/c150d9a251a2b3442291d84de8d0af64 to your computer and use it in GitHub Desktop.
Django: reorder by model IDs
from typing import Type, List
from django.db import models, connection
class OrdererByID:
"""Helper class to update rows in table according to order of list of IDs provided."""
def __init__(self, *, model: Type[models.Model], field_name: str) -> None:
self.reorder_query = f"""
UPDATE "{model._meta.db_table}"
SET "{field_name}" = "input_data"."order"
FROM (
SELECT
unnest(%s) AS id,
unnest(%s) AS order
) AS "input_data"
WHERE ("{model._meta.db_table}"."id" = "input_data"."id")
"""
def update_order(self, ids: List[str], *, start_from: int = 1) -> int:
if not ids:
return 0
with connection.cursor() as cursor:
cursor.execute(self.reorder_query, [ids, list(range(start_from, start_from + len(ids)))])
return cursor.rowcount
class SomeModel(models.Model):
...
order = models.IntegerField() # this field can be vary
...
orderer = OrdererByID(model=SomeModel, field_name='order')
orderer.update_order([5, 2, 7, 3, 4])
# ... leads to followign result in DB:
# [
# SomeModel(id=5, order=1),
# SomeModel(id=2, order=2),
# SomeModel(id=7, order=3),
# SomeModel(id=3, order=4),
# SomeModel(id=4, order=5),
# ]
# You may need to refresh objects from DB or just update attrs
# (not implemented in this snippet)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment