Recursion to retrieve blog thread - help please
I feel I should know how to do this, but I haven't really had to write a function like this yet. We have a blog site where, currently, users can post entries to their own respective blog, and replies can be made to that entry. Entries are kept in an entries table, and replies are kept in a replies table. Each reply belongs to a parent entry, so entryId is a foreign key within the replies table. I've been tasked with modifying the site so that replies can be made to replies, so you can have multi-level threads. I've done this, by adding parentReplyId as a column in the replies table. Each reply still has the entryID of the entry that ultimately started the thread to which it belongs, but now parentReplyId gives additional info - if this is null, then the reply is a top-level reply, made to an entry directly, and if it is not null, then the reply has been made to another reply. Now it comes down to writing a cfc function that will retrieve an entry's reply nodes in the correct order, following each reply down the tree until it dead ends and then continuing on at the next level up (I'm sure there's a more technical term for this type of structure). So ultimately I'll be able to display reply A on the page with its child replies indented below it, and any of their child replies further indented below them, etc., until there are no more and its on to reply B...like how you see replies displayed on Daily Kos and Livejournal. I'm really not sure how this is typically done in a CFC function, or if I'm best off with returntype query, or returntype array, or if maybe this can be done in a single SQL query. But I've a strong feeling that it's done with recursion. Help please? Thanks, Christophe ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314869 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Recursion to retrieve blog thread - help please
Original call would look something like this, where entryID is all you need to start with: cfset myReplyTreeQry = getChildren(url.entryID, request.datasource) This would create a query result called myReplyTreeQry with the following columns: entryID replyID (of each reply) parentReplyID (of each reply, not really needed, but good for debugging) level (of each reply, to help in indenting as you output them) Obviously, more columns would need to be added to show all the actual Reply information, but this works for the recursive structure. cffunction name=getChildren returntype=query output=no cfargument name=entryID type=string required=yes cfargument name=datasource type=string required=yes cfargument name=parentReplyID type=string required=no default= cfargument name=rtnQuery type=query required=no cfargument name=levelCtr type=numeric required=no default=0 cfset var ds = arguments.datasource cfset var rtn = cfset var childQry = cfset var level = arguments.levelCtr cfif not structKeyExists(arguments, rtnQuery) cfset rtn = queryNew(entryID, replyID, parentReplyID, level) cfelse cfset rtn = arguments.rtnQuery /cfif cfquery datasource=#ds# name=childQry SELECT e.entryID, r.replyID, r.parentReplyID FROM entries e LEFT OUTER JOIN replies r ON e.entryID = r.entryID WHERE e.entryID = cfqueryparam cfsqltype=CF_SQL_VARCHAR value=#arguments.entryID# AND r.parentReplyID = cfquerparam cfsqltype=CF_SQL_VARCHAR value=#arguments.parentReplyID# /cfquery cfif childQry.recordCount cfset level = level + 1 cfoutput query=childQry cfset queryAddRow(rtn) cfset querySetCell(rtn, entryID, entryID) cfset querySetCell(rtn, replyID, replyID) cfset querySetCell(rtn, parentReplyID, parentReplyID) cfset querySetCell(rtn, level, level) !--- now call this function recursively --- cfset rtn = getChildren( arguments.entryID, ds, replyID, duplicate(rtn), level ) /cfoutput /cfif cfreturn rtn /cffunction ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314872 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Recursion to retrieve blog thread - help please
Thank you very much - this works beautifully! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314889 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
