Автоматическая сортировка строк с вспомогательным порядковым столбцом — средствами MySQL

[TimTowdy]

По-моему всё это можно выполнить одним запросом.

[o_O_Tync]

Я бы тоже не отказался. Правда.

[kurokikaze]

Надо делать self-join. Сейчас попробую написать.

[Rafael_Delon]

Хорошо что возникла «интересная» задача! И автор написал пост.)

[JayDi]

/*сортировка товара по алфавиту*/
SET @rownum := 0;
UPDATE
 products p
SET
 p.order_num = (SELECT
  temp.new_order_num
 FROM
  (SELECT
   p2.product_id,
   ((@rownum := @rownum + 1) * 10) AS new_order_num
  FROM
   products p2
  ORDER BY
   p2.model) AS temp
 WHERE
  temp.product_id = p.product_id)

* This source code was highlighted with Source Code Highlighter.

[o_O_Tync]

Наверное вы правы. Однако документация последней MySQL гласит, что нельзя одновременно делать UPDATE таблицы, из которой происходит чтение в подзапросе :)

[JayDi]

Там же в комментариях к документации пишут, что такое ограничение обходится путем вынесения подзапроса на 2 уровня вглубь — в этом случае данные полностью материализуются и могут использоваться в обновлении. У нас именно такой запрос и получился — работает на 5.0 сервере.

[o_O_Tync]

Любопытно, у меня на 5.1.37 выдаётся ошибка «Can't reopen table: 'p'». Могли ошибиться с адаптацией примера, но это неважно: идея понятна, спасибо :) Правда, сработает она только если допустимо присвоение своих чисел в столбец сортировки, а я писал что этого делать нельзя: сортируются только товары в одной категории. Если таким запросом сортировать несколько категорий раздельно — получатся повторы в столбце сортировки.

[o_O_Tync]

Приятно, что у вас такие запросы пишутся с полпинка. А я-то дурак рассказываю про приёмы которые нагуглить непросто… ;)

[pwlnw]

Ну не постить же теперь тут главы из всем известных книжек?
У автора возникла проблема и он ее оригинально решил. И комментарии к этой конкретной зарисовке довольно любопытны.

[pwlnw]

Чтобы быть до конца уверенным, напишите же как именно следовало тут поступить без применения переменных. Кстати они не глобальные, а сессионные.

[o_O_Tync]

Я пробовал вариант «CREATE TEMPORARY TABLE `by_model` (… AUTO_INCREMENT… ) SELECT ...» но по какой-то причине отмёл его. Вспомню причину — поделюсь :) Вы правы, в общем случае этого будет достаточно, но есть и минус: придётся описать все поля временной таблицы вручную. Лишний труд имхо

[o_O_Tync]

В данном случае всё просто, мускл вполне верно выбрал тип обоих полей INT. Спасибо, буду знать что не всё так сахарно :)

[life4beer]

А можно пояснить, почему из «в частном случае проводилась сортировка товаров одной категории» следует, что «нельзя использовать порядковые 0,1,2,… — для использования годится только id товара.»
Все равно ведь айдишки продуктов попадают в колонку order_num случайным в общем то образом и ситуации, когда айди продукта из одной категории попадает в ордер_нум продукта из другой категории, будут! Значит колонку order_num для определения категории использовать нельзя и… и 0,1,2 получается тогда ничем не хуже?

[o_O_Tync]

Не, не будут :) парочка дополнительных WHERE выберет только id товаров из нужной категории, и всё будет пучком :)
Конкретно — в оба подзапроса ставится «WHERE `category_id`=69». Этот момент я опустил чтоб не загромождать суть частностями

[widowmaker]

А зачем вообще мучаться???
Можно вполне даже без подзапросов обойтись:

SET @order := 0;
UPDATE `products` SET `order_num` = @order := @order + 10 ORDER BY `model` ASC;

* This source code was highlighted with Source Code Highlighter.

[widowmaker]

Для надёжности лучше даже

SET @order := 0;
UPDATE `products` SET `order_num` = @order := (@order + 10) ORDER BY `model` ASC;

* This source code was highlighted with Source Code Highlighter.

[LoneCat]

Ну видимо автор забыл что UPDATE тоже имеет сортировку, соот-но и пытался задачу сортировки переложить на SELECT, что добавило своих ограничений. Я, читая условия задачи, тоже об этом не вспомнил, все-таки не так часто это используется, или в случае когда есть внутренний счетчик, или когда обновление/удаление ограниченно через LIMIT.

[o_O_Tync]

Видимо вы невнимательно читали топик. «Замечу, что… нельзя использовать порядковые 0,1,2,… — для использования годится только id товара.». Глупый движой поставил мне жёсткие рамки :)

[LoneCat]

Ограничение из-за того что поле сортировки должно быть уникально по всей таблице, а пересортировка может производится отдельно по категориям?

[o_O_Tync]

Именно :)

[widowmaker]

Да, уже вник в смысл танцев с бубном. Чтобы было понятнее данные надо немного изменить, тогда станет понятнее, например, вместо 40 поставить 70.

P.S. Надо будет в следующий раз внимательнее читать.

[widowmaker]

P.S.S. а еще надо взять за привычку перечитывать свой комментарий перед отправлением :(

[o_O_Tync]

Вы правы, изменил для наглядности. Спасибо! :)

[Methos]

Но ведь можно поддерживать уникальный order_num при вставке новой записи. То есть, при вставке берётся максимальный и увеличивается на единицу.

[o_O_Tync]

В принципе да, можно. Единственное что мне в этом не нравится — теоретически неограниченный рост столбца order_num :)