Как организовать изменение порядка строк в таблице?

Question

[Евгений Трофимов]

Есть таблица в mysql вида id,заголовок,текст.
Выводятся записи в виде списка, <li id="item_'.$row["id"].'"> , а при добавлении новой записи id = $query->insert_id.
Хочу сделать, чтобы пользователь мог менять порядок записей с помощью drag and drop. Если правильно понял - для этого нужно добавить еще одно поле, поле приоритета и при выводе записей сортировать по нему.
Но, тут у меня пара проблем возникла -
1) Значение этого поля по умолчанию - я не могу сделать его автоинкрементируемым, так как уже есть id. Как быть?
2) Я посылаю на сервер два id записей, которые мне нужно поменять местами. Т.е. приоритета я не знаю. Логичным кажется сделать <li id="item_'.$row["priority"].'"> , тогда и на сервер я смогу посылать именно priority, но мне нужно как-то узнать значение поля priority у добавляемой записи(ну т.е последней записи).. Какой запрос для этого лучше всего подойдет?

Answer 1

[Stalker_RED]

Похоже, что какого-то красивого решения нет. Если вас устроит блокировка при вставке, то можно сделать как-то так:

LOCK TABLES `test_sort` AS t1 WRITE, `test_sort` WRITE;

INSERT INTO `test_sort` (`title`, `order`) 
SELECT 'YOUR_TEXT_HERE', IFNULL(MAX(`order`), 0) + 1 FROM `test_sort` AS t1;

UNLOCK TABLES;

схема

CREATE TABLE `test_sort` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `order` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

Т.е. при вставке нового значения выбирается максимальное значение order и добавляется +1.
Если уникальность этого порядка не очень то и важна, ну подумаешь, то можно и без блокировки.

А поменять приоритет двух записей зная только их id можно так:

UPDATE test_sort AS t1
JOIN test_sort AS t2 ON ( t1.id = 1 AND t2.id = 4 )
SET
  t1.order = t2.order,
  t2.order = t1.order;

Comments

[Евгений Трофимов]

Спасибо, получилось) А если я хочу не местами поменять, а изменить весь порядок сортировки, например было (1,2,3,4,5) , станет (2,3,1,4,5) - то нужно передать серверу весь массив новых значений order и делать запрос на обновление для каждого элемента?

[Stalker_RED]

Евгений Трофимов: Да, серверу нужно передать весь список. а обновлять можно и одним запросом

UPDATE mytable
SET (title='Great Expectations' WHERE id='1'),
(title='War and Peace' WHERE id='2');

Answer 2

[Rsa97]

Если изначально установить priority = id и только менять их местами, то очевидно, что max(priority) = max(id). А значит при добавлении следующей строки можно опять установить priority = id с помощью триггера AFTER INSERT.