Re: Query to select nested comments sorted by nesting and date

The result needs to be sorted by date of most recent reply descending
(replying bumps the thread) but also need to be sorted such that the
parent/child relationships are maintained. Multiple replies on the same
level also need to be sorted by date desc.

Getting the parent/child sorting can be accomplished with a simple "order by
id_tree", but I can't find any way to combine that with date sorting without
breaking the nesting.

What is the most efficient way of making this work? I experimented with
"WITH RECURSIVE" but it won't allow me to sort until the end so it doesn't
seem to help. Plus it appears to be much slower than just using the ltree
(100ms for ltree based vs 1.5 seconds for WITH RECURSIVE). I could do the
sorting in the php code, but it seems more efficient if I can just do it all
in a single SQL query.

I follow generally what you are trying to do. One possibility to the
perform the SELECTION (using ltree) first. Then manipulate the results so
that you have information such as depth, parent, and postingdate. Using
those fields you should then be able to sort as you need to.

It may be worth denormalizing such that the root of a thread has "most
recent child posting date" so that you can order the parents directly and
also have "root parent" and "direct parent" pointers.

Mind you I have never developed (only seen/used) message-board/threading
code before.but I figure I might as well throw some ideas out there while
waiting for more informed people to respond.

David J.