Conflicto en ordernacion [Hierarchical data]

[TMeister]

Hola gente, tengo un pequeño problema el cual me esta dando lata.

Cuento un poco el contexto, Estoy trabajando en un sistema de comentarios en el cual una respuesta puede tener múltiples respuestas con niveles ilimitados. como sabran los niveles pueden dar muchos dolores de cabeza, recursividades carga al server etc.

Estuve trabajando en un sistema en donde para facilitar las consultas agrege 2 campos lineage y deep para poder hacer el ordenamiento desde una solo consulta no importando los niveles existentes.

La estructura de la tabla obviando los demas campos que no importan por ahora es:

Código MySQL:

+--------------+--------------------+------+-----+---------+----------------+
| Field        | Type               | Null | Key | Default | Extra          |
+--------------+--------------------+------+-----+---------+----------------+
| id           | int(10)            | NO   | PRI | NULL    | auto_increment |
| parent_id    | int(10)            | YES  |     | NULL    |                |
| lineage      | varchar(255)       | NO   |     | NULL    |                |
| deep         | int(3)             | NO   |     | NULL    |                |
+--------------+--------------------+------+-----+---------+----------------+
 

al momento de insertar un nuevo comentario sin padre la tabla queda asi:

Hola gente, tengo un pequeño problema el cual me esta dando lata.

Cuento un poco el contexto, Estoy trabajando en un sistema de comentarios en el cual una respuesta puede tener multiples respuestas con niveles ilimitados. como sabran los niveles pueden dar muchos dolores de cabeza, recursividadesm carga al server etc.

Estuve trabajando en un sistema en donde para facilitar las consultas agrege 2 campos lineage y deep para poder hacer el ordenamiento desde una solo consulta no importando los niveles existentes.

La estructura de la tabla obviando los demas campos que no importan por ahora es:

Código MySQL:

+----+-----------+----------+------+
| id | parent_id | lineage  | deep |
+----+-----------+----------+------+
| 15 |      NULL | 15       |    0 |
+----+-----------+----------+------+
 

En donde el lineage es el mismo que id el cual nos servirá para despues hacer el ordenamiento.

Cuando este comentario a su vez recibe comentarios el resultado es este:

Código MySQL:

+----+-----------+----------+------+
| id | parent_id | lineage  | deep |
+----+-----------+----------+------+
| 15 |      NULL | 15       |    0 |
| 36 |        15 | 15-36    |    1 |
| 34 |        15 | 15-34    |    1 |
| 35 |        15 | 15-35    |    1 |
| 68 |        34 | 15-34-68 |    2 |
| 67 |        34 | 15-34-67 |    2 |
| 16 |      NULL | 16       |    0 |
| 17 |      NULL | 17       |    0 |
| 18 |      NULL | 18       |    0 |
| 19 |      NULL | 19       |    0 |
+----+-----------+----------+------+
 

Esto es el resultado de la siguiente consulta

Código SQL:

SELECT c.id, c.parent_id, c.lineage, c.deep FROM comments AS c 
WHERE c.note_id = 157 AND c.STATUS = 'publish' ORDER BY c.lineage + 0 LIMIT 0,10;
 

Aquí viene el problema, el order by c.lineage lo hace relativamente bien.

Como podrán ver estoy forzando al ordenamiento numérico con el + 0 pero el resultado no es el deseado.

Ahora haciendo el ordenamiento natural obtengo:

Código SQL:

SELECT c.id, c.parent_id, c.lineage, c.deep FROM comments AS c 
WHERE c.note_id = 157 AND c.STATUS = 'publish' ORDER BY c.lineage LIMIT 0,10;
 

Código MySQL:

+-----+-----------+----------+------+
| id  | parent_id | lineage  | deep |
+-----+-----------+----------+------+
| 100 |      NULL | 100      |    0 |
|  15 |      NULL | 15       |    0 |
|  34 |        15 | 15-34    |    1 |
|  67 |        34 | 15-34-67 |    2 |
|  68 |        34 | 15-34-68 |    2 |
|  35 |        15 | 15-35    |    1 |
|  36 |        15 | 15-36    |    1 |
|  16 |      NULL | 16       |    0 |
|  17 |      NULL | 17       |    0 |
|  18 |      NULL | 18       |    0 |
+-----+-----------+----------+------+
 

Ahora el lineage "interno" es decir los 15-34-67 los ordena bien PERO el orden pone al 100 menor que el 15

El ordenamiento esperado es:

Código MySQL:

+----+------------+----------+------+
| id  | parent_id | lineage  | deep |
+----+------------+----------+------+
|  15 |      NULL | 15       |    0 |
|  36 |        15 | 15-36    |    1 |
|  34 |        15 | 15-34    |    1 |
|  68 |        34 | 15-34-68 |    2 |
|  67 |        34 | 15-34-67 |    2 |
|  35 |        15 | 15-35    |    1 |
|  16 |      NULL | 16       |    0 |
|  17 |      NULL | 17       |    0 |
|  18 |      NULL | 18       |    0 |
|  19 |      NULL | 19       |    0 |
| 100 |      NULL | 100      |    0 |
+-----+-----------+----------+------+
 

Alguna idea al respecto??

Umm tal vez haciendo un order by id and order by lineage, eso se me ocurrió en este momento pruebo y vuelvo aunque de todos modos dejo el post por si alguien quiere echar una mano..

Saludos!