Estoy escribiendo un sistema de comentarios. Pude obtener la jerarquía siguiendo el tutorial aquí ( http://www.postgresonline.com/journal/archives/173-Using-LTree-to-Represent-and-Query-Hierarchy-and-Tree-Structures.html )
SELECT n.node_path AS PATH, n.note_id AS _1, n.note_no AS _2, n.public_key AS _3, n.upvotes AS _4 FROM public.comment_table AS n INNER JOIN public.comment_table AS a ON (a.node_path @> n.node_path) GROUP BY _1, PATH ORDER BY PATH Sin embargo, tengo problemas para ordenar la fila por voto a favor. No puedo simplemente ORDER BY PATH, n.upvotes aquí porque las respuestas en el mismo hilo tendrán diferentes node paths . node_paths se calculan usando public_key .
Del ejemplo, dado
a (0 upvotes) -> b (0 upvotes) -> c (1 upvote) d (1 upvote) -> e (0 upvotes) La ruta del nodo para b y c será ab y ac respectivamente. No puedo simplemente restar b y c de la ruta del nodo y ORDER BY ellos. Si lo hago, resultará en este orden:
a d -> b -> c -> e Esto tiene sentido porque si elimina la clave pública de cada fila de la public_key del node_path , simplemente ordenará por la ruta del node_path más corta a la más larga.
¿Cómo escribo una consulta que resulte en la jerarquía correcta y ordenada por votos a favor así:
d (1) -> e (0) a (0) -> c (1) -> b (0)Suponiendo que los recuentos de votos a favor cambian constantemente y que seguiste la ruta ltree para evitar la recursividad, no puedo pensar en una solución para esto que irónicamente no requiera recursividad ya que cada fila necesita tener acceso a los recuentos de votos a favor de sus antepasados para encontrar dónde ubicarse en los resultados.
with recursive base as ( select node_path, upvotes, array[row_number() over (order by upvotes desc, node_path)] as sort_path from comment_table where nlevel(node_path) = 1 union all select c.node_path, c.upvotes, p.sort_path||row_number() over (order by c.upvotes desc, c.node_path) from base p join comment_table c on subpath(c.node_path, 0, -1) = p.node_path ) select * from base order by sort_path; node_path | upvotes | sort_path -----------+---------+----------- d | 1 | {1} de | 0 | {1,3} a | 0 | {2} ac | 1 | {2,1} ab | 0 | {2,2} (5 rows)