Допустим, есть вот такая табличка (для простоты опустим индексы и т. п.):
CREATE TABLE items (
id BIGSERIAL PRIMARY KEY,
category_id BIGINT NOT NULL,
name VARCHAR NOT NULL,
position INT NOT NULL
);
SELECT name FROM items WHERE category_id = ? ORDER BY position
Встаёт вопрос как дать пользователю возможность менять порядок элементов. Допустим, на фронте реализован интерфейс с drag'n'drop и кнопкой «Сохранить изменения», при нажатию на которую бек получает category_id и список id всех элементов этой категории в новом порядке. Теперь беку нужно обновить значения полей position в БД (элементов в каждой категории не очень много, так что мне кажется допустимым перезаписать position у всех элементов в заданной категории).
Я пока вижу тут только решение в лоб с отдельным UPDATE для каждой записи, а значения position вычислять в коде (банальный цикл for по индексу в массиве id c фронта и использовать счётчик цикла). Но мне кажется такой подход не очень эффективным, так как будет N запросов, к тому же он плохо уживается с уникальным индексом на category_id + position (либо такой индекс надо убрать, либо использовать deferred индексы и т. п.).
Есть ли какие-то решения лучше?
P. S.: СУБД PostgreSQL
