Как сортировать нумерованый список в запросе mysql?

[simbatron]

Здравствуйте.
Есть таблица, в ней колонка decimal_id, в ней следующие значения:
1
1.1
1.1.1
1.1.2
1.2
1.1.3
1.1.4
1.2.1
2
Вопрос: Как может выглядеть запрос чтоб данные сортировались по колонке decimal_id и получался нумерованный список как в ворде. То есть должно получиться так:
1
1.1
1.1.1
1.1.2
1.1.3
1.1.4
1.2
1.2.1
2

1.2 переехала вниз

числа между точками могут быть многозначными

 

[movRAX]

Думаю, в самом запросе это будет проблематично реализовать, если вообще возможно.

 

[simbatron]

Может кто нибудь знает элегантный способ это сделать в PHP?
Например я могу сделать массив, где ключ эти поля decimal_id а значения это остальные поля таблицы.
??

 

[Вурдалак]

Стоит подумать над другим способом хранения decimal_id.

Вариант 1-й: дополнять нулями старшие разряды (например, «002.00000.00» — зависит от диапазона возможных значений чисел) .
Вариант 2-й: хранить в трёх отдельных полях.
Вариант 3-й: можно составить функцию, однозначно переводящую это выражение в целое число и обратно.

 

[fixxxer]

Дополнительное поле bigint decumal_sort_id + триггер на insert/update, вычисляющий что-то типа
$result = 0;
$multiplier = 1;
$mul_step = 1000;
foreach(explode('.', $decimal_id) as $item) {
$result += $item * $multiplier;
$multiplier *= $mul_step;
}
return $result;
?

 

[Вурдалак]

Chusha, а ты как думаешь?

 

[Вурдалак]

Если никак не думаешь, то и пытаться отвечать не надо.

 

[simbatron]

Что-то я не понял проблемы Вроде order by ещё не отменили.
Какой тип у вашего "decimal_id" ?

decimal_id это tinytext

просто order by не катит, например эти значения будут отсортированы неправильно:
1.2.1
1.12.1

а совместно с

Стоит подумать над другим способом хранения decimal_id.
Вариант 1-й: дополнять нулями старшие разряды (например, «002.00000.00» — зависит от диапазона возможных значений чисел) .

может получиться

как я сам не подумал об этом, спасибо Вурдалак
проверю и отпишусь здесь

А вообще, раз автор молчит/исчез значит ему уже не надо, то и говорить не о чем. Проехали.

да я просто сплю ночью, а днем работаю

 

[simbatron]

Проверил.

Вариант 1-й: дополнять нулями старшие разряды (например, «002.00000.00» — зависит от диапазона возможных значений чисел) . от Вурдалак
+ order by

вроде прекрасно работает.

Если же число уровней очень ограничено, то Вариант 2-й от Вурдалак-а ещё лучше.

число уровней не ограничено
зачем

Это сортировка комментариев к статье.
Наверно тему можно закрыть.

 

[Вурдалак]

Это не лучший вариант, т.к. длина ключа будет неоправданно большая для такого значения. Лучше всего воспользоваться какой-то функций для перевода значения в целое число и обратно.

 

[Вурдалак]

Так всё равно придётся писать две функции — для добавления нулей и их тримминга. Так почему сразу в int и обратно не переводить?

 

[simbatron]

Лучше всего воспользоваться какой-то функций для перевода значения в целое число и обратно.

даже не представляю как может это выглядеть.

а второй вариант + group by я уже реализовал, он работает, число уровней не ограничено, число комментариев в одном уровне сделал максимально 999 (в данной ситуации это тоже можно считать как не ограничено, столько точно не накомментируют) и никаких дополнительных колонок не нужно, а перерасход лишних байтов мне представляется пренебрежительно мал в этом случае.

а Вам Вурдалак спасибо за совет

 

[Вурдалак]

Да ничем от хранения IP это не отличается:

function packVersion($s)
{
    $a = explode('.', $s);

    return (($a[0] & 0xFF) << 24) | (($a[1] & 0xFFFF) << 8) | ($a[2] & 0xFF); 
}

function unpackVersion($i)
{
    $x = ($i >> 24) & 0xFF;
    $y = ($i >>  8) & 0xFFFF;
    $z = $i & 0xFF;

    return "{$x}.{$y}.{$z}"; 
}

Тут, например, для старшего и самого младшего числа выделено по 8 байт, а для среднего — 16.

 

[simbatron]

group by зачем?

прошу прощения, конечно order by

Да ничем от хранения IP это не отличается:
Тут, например, для старшего и самого младшего числа выделено по 8 байт, а для среднего — 16.

Да теперь представляю, проверил Ваш код, если сдвигами битов упаковать этот ключ то получается существенная экономия памяти и процессорного времени, и вообще код проще и красивее.
Но возникает проблема: я достаю из базы ключи (с помощью decimal_id RLIKE '^001.002.[0-9]+$' ) 001.002.001, 001.002.002, 001.002.003 - это комментарии к 001.002, потом беру последнее из них, увеличиваю на 1, и получаю 001.002.004 - это ключ для нового комментария.
Не представляю как получать последнее значение в уровне, ели ключи представлены в INTEGER?

 

[Вурдалак]

simbatron, про auto increment слышал?

 

[simbatron]

simbatron, про auto increment слышал?

001
001.001
001.001.001
001.001.002
001.002
001.001.003
001.001.004
001.002.001
002
001.002.001.001
001.002.002
001.002.002.001
отсортировано по полю auto increment

а должно быть так:
001
001.001
001.001.001
001.001.002
001.001.003
001.001.004
001.002
001.002.001
002
001.002.001.001
001.002.002
001.002.002.001

001.002 должно быть после 001.001.004

или я не так Вас понял?

 

[simbatron]

к тому же проблема сортировки может быть успешно решена двумя способами:
Вариант 1-й: дополнять нулями старшие разряды (например, «002.00000.00» — зависит от диапазона возможных значений чисел) .
Вариант 3-й: можно составить функцию, однозначно переводящую это выражение в целое число и обратно. (здесь сортировка лучше).
И теперь с сортировкой проблем нет.
Но использовать вариант 3-й не представляется возможным из за того что при его использовании возникает непреодолимая (по моему) проблема с выборкой по decimal_id, которую я описывал выше.

 

[Вурдалак]

Какая-то чушь. Auto-increment — это свойство поля, а не поле, поэтому «сортировать по auto-increment» — бессмыслица.

 

[simbatron]

Какая-то чушь. Auto-increment — это свойство поля, а не поле, поэтому «сортировать по auto-increment» — бессмыслица.

в моей таблице есть поле num_id и оно имеет свойство Auto-increment.
просто я думал поймешь

 

[fixxxer]

001
001.001
001.001.001
001.001.002
001.001.003
001.001.004
001.002
001.002.001

1) определяемся с максимальным уровнем вложенности, пусть 4
2) дополняем нулями:

001.000.000.000
001.001.000.000
001.001.001.000
...

3) меняем точку на 1, дописываем в начале 1

1001100010001000
1001100110001000
1001100110011000
...

4) получили нужный нам bigint

можно оптимальнее но не вижу особого смысла. один хрен btree примерно одинаково.