> What would the SQL statement look like.
Steve,
It depends on some other factors.
(1) If you're going to be retrieving a small and fairly consistent set of
messages in the same way, you might use a recursive custom tag containing a
cached query. It's ugly, but it gets the job done.
(2) Then there's the Nested Set Model... instead of relying on ParentIDs to
track relationships, you use left and right boundary fields that describe
where a given message lives within the heirarchy. S. Nelson is quite fond of
it, and for certain uses, so am I... if you need to track sibling
relationships along with the standard parent/child stuff, there's no better
game in town. But the NSM *does* make things like moving tree branches
around rather cumbersome, and you'll still need to track ParentIDs if you
want to do anything interesting with the results of a NSM SELECT.
See http://www.dbmsmag.com/9603d06.html or http://www.secretagents.com/ for
more.
(3) Personally, I use the old flat/nested structure trick... it violates
some folks' sense of purity by making CF do a lot of post-SELECT data
massage, but it's the most flexible approach IMO, and because it doesn't
require any recursion until you're ready to display the tree, it can be
much, much faster than method 1. And it'll probably become even more useful
after CF5 arrives. See if this psuedo-code makes sense:
<cfset flat = StructNew()>
<cfset threaded = StructNew()>
<cfquery name="messages">
SELECT a batch of IDs and ParentIDs
</cfquery>
<cfloop query="messages">
<cfscript>
message = StructNew();
message.id = id;
message.parentid = parentid;
message.author = author;
message.text = text;
flat["m#id#"] = message;
if (StructKeyExists(flat, "m#parentid#")) {
flat["m#parentid#"]["m#id#"] = flat["m#id#"];
} else {
threaded["m#id#"] = flat["m#id#"];
}
</cfscript>
</cfloop>
The result is that you end up with two structures full of references to each
other... one (threaded) that can be walked recursively for display purposes,
and another (flat) that provides random access to any point in the tree
without any recursion or looping at all.
--
Roger
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
