Well I've run into a problem it appears. I spent quite awhile working out a threaded model for a comments system I'm launching on my site, that would let me grab entire trees et al with single queries. What I've come up with works awesome.
All but for one thing. I want to inform everybody of how many posts exist beneath a certain thread. I do *not* want to do additional queries for each top level post just to see what's all beneath it. I want to figure out a way to do this with the original query, or at most with one additional query.
A general idea of how my threaded model works is that all top level posts are given an id of XXXXX - every level of thread below that is added on to that id. One level deep would be XXXXX.XXX, two levels deep XXXXX.XXXXXX and so on. This is a very rough explanation of course, since I'm not providing very detailed examples, however it should be enough to work on.
You can poke around the system as I build it at http://www.projectlinux.org/results.phtml
- please *expect* to run into problems if you are playing with it, it's in beta and I'm constantly adding things and ripping things out right now. =)