Как изменить порядок записей в БД

Рейтинг: 1Ответов: 1Опубликовано: 14.05.2023

До сих пор не понимаю как правильно изменять порядок записей в бд. Например на сайте есть галерея с фото пользователя, но пользователь реши поменять порядок картинок. В бд таблица photos со столбцами id, date_creation, id_user, photo_url

Известные мне способы реализации:

  1. добавить дополнительный столбец list_order. В нём будет целое число по которому и будет осуществляться сортировка, фото которое нужно вывести первым будет иметь list_order = 1, второе 2 и так далее. Проблема данного способа в том что если пользователь передвинет фотку из начала в самый конец то нужно будет обновить все list_order между ними.

  2. как и первом пункте добавить дополнительный столбец list_order. Но теперь могут быть не только целые но и дробные числа. У новых фото можно добавлять list_order с некоторым интервалом, например у первой фотографии 10, у второй 20, у третьей 30 и тд. Теперь при перемещении третьего фото в позицию между первой и второй фотографией list_order станет ((list_order предыдущего фото + list_order следующего фото) / 2) = (10+20)/2 = 15. Так же число может быть и не целым. Это решает проблему, теперь мы обновляем только одну запись, но появляется потенциальная проблема что при определённых перемещениях случится что то подобнее 15 12,5 11,25 10,625 10,3125 10,15625 10,078125 10,0390625 10,01953125 10,00976563 10,00488281 10,00244141 10,0012207 10,00061035 10,00030518 10,00015259 10,00007629 10,00003815 и так далее... И тут число может стать очень маленьким и выйти за пределы float. Даже если преобразовать конечное число например в text (на самом деле тут нужно по особому преобразовывать но этот момент опущу) то проблема отодвинется, но всё равно остается.

  3. сделать связанный список. Теперь добавляется 2 новых столбца previous_id и next_id которые ссылаются на предыдущее и следующее фото соответственно. У первого фото previous_id = 0, у последнего next_id = 0. Это лучший способ, однако возникает проблема с выводом ограниченного числа фото. Если вывести все фото то можно отсортировать их на клиенте, но как вывести например первые 10 фото?

В общем я до сих пор не нашел в интернете нормального решения как поступать в подобном случае.

sql mysql mysqli
Источник: Stack Overflow на русском

Ответы

▲ 1

Не стоит полагаться на какую либо сортировку в таблице, она не гарантируется от слова совсем и никак не настраивается.

В данном случае я бы добавил столбец position и ориентировался бы на него, как в вашем 1 варианте.

2 вариант интересный, но это кажется мне преждевременной оптимизацией. Стоит попробовать первый вариант и если производительности не хватит, то пробовать дробные значения, только вместо float я бы выбрал decimal.

По 3 варианту я вижу только вывод рекурсией с параметром, который укажет запросу глубину рекурсии, чтобы выбрать TOP N. Но тут нужно будет учесть дефолтные настройки БД по поводу глубины рекурсии и в запросе явно это указать хинтом(в MS SQL для этого есть хинт OPTION (MAXRECURSION 110)), наверняка для MySQL есть что-то подобное. Но от 3 варианта я бы всё-тики отказался: слишком сложно разрабатывать, отлаживать, поддерживать. Да и по производительности профита скорее всего почти не будет.

В общем начинайте от простого к сложному по принципу KISS и отказа от преждевременной оптимизации.