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!

[Vun]

Tu solucion esta clara!! guarda los ids de lineage con 3 cifras (o mas) forzosamente, es decir algo asi:

100
015
015-036
015-014
015-034-068

De esa forma al ordenarlo alfanumericamente lo vas a tener como quieres ya que 015 va antes que 100.

De hecho yo uso esta funcion en mi juego para algo muy similar a lo que tu dices:

Código PHP:

  // Funcion que agrega ceros a la izquierda de otra variable segun longitud dada 
function agregaceros($longitud,$variable) { 
    return str_repeat("0",$longitud-strlen($variable)).$variable; 
}  
   

[GeoAvila]

bueno sería algo así

Código MySQL:

select * from tabla order by Convert(id,SIGNED)
 

nos vemos.

[edmret]

has probado, usando tambien deep?

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,c.deep LIMIT 0,10;

[TMeister]

@Vun suena lógica esa solución Voy a probar a ver que tal.

@GeoAvila Ahi estamos forzando el ordenamiento a que sea numerico que exactamente igual que lo que hago con el order by lineage + 0 da el mismo resultado.

Gracias a ambos.. ya les cuento.