ya, sorry, I noticed that afterwards. I don't play in cf daily so I forget some of the functionality. in this one particular case, query of queries is a very nice choice.
Bruce Dunwiddie Ticket Technology P: 866.543.3331 F: 913.451.1786 [EMAIL PROTECTED] -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of doug boude Sent: Tuesday, July 13, 2004 6:28 PM To: [EMAIL PROTECTED] Subject: RE: [KCFusion] recursive function help needed Thanks Bruce, I'll play with your suggestions tonight. But I must protest being a suspect of database abuse...my cfquery function performs a query of queries...the database will only be touched once initially. :0) Doug :0) >From: "Bruce Dunwiddie" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: <[EMAIL PROTECTED]> >Subject: RE: [KCFusion] recursive function help needed >Date: Tue, 13 Jul 2004 17:56:13 -0500 > >I can't see anything obviously wrong with your logic, other than needing to >arrest you for defenseless database abuse, but I do have an idea for you to >track down the problem. You're currently assuming that you don't need an >else statement for the end of the block " if(thisquery.recordcount gt >0){//this individual supervises someone...", which that if statement >shouldn't even be needed, but I would suggest you put in an else statement >on that block that says writeout( "we shouldn't ever get here.<br>" ) and >see if that's where your recursion loop is ending up. Next, I would a line >directly under the line "for (i=1; i lte thisquery.recordcount; >i=i+1){//loop through thiskey's >subordinates..." that says writeout( "i:" + i + " recordcount:" + >thisquery.recordcount + "<br>" ) since those are your looping dependencies, >and you should notice something obviously wrong. The correct output should >be like >i:1 recordcount:3 >i:2 recordcount:3 >i:3 recordcount:3 >i:1 recordcount:3 >i:2 recordcount:3 >i:3 recordcount:3 >i:1 recordcount:1 > >based on your test data. The next thing I would suggest is running the code >in debug and just stepping through it. > >Bruce Dunwiddie >Ticket Technology >P: 866.543.3331 >F: 913.451.1786 >[EMAIL PROTECTED] > > >-----Original Message----- >From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] >Behalf Of doug boude >Sent: Tuesday, July 13, 2004 4:30 PM >To: [EMAIL PROTECTED] >Subject: [KCFusion] recursive function help needed > > >I've spent a couple of days on this and am at a dead end, so I appeal to >the >wise sages of the CF council for advice and enlightenment. > >Objective: take a hierarchical query and output it in a tree format. > >Incoming query structure: > >ID Parent_ID > >hdb4239 dre8714 >pkj4567 dre8714 >kxj4231 dre8714 >lat9876 hdb4239 >sat7787 lat9876 > >At this point, I want to do absolutely nothing fancy, and am trying just to >get it to work in its bare-bones form, which is to simply write text to the >page in the form of: > >top level person >-second level person1 >--third level person1 >--third level person2 >-second level person2 >etc. > >My approach: > >1. Create an initial query containing the set of hierarchical data i want >to >output (in this case, I'm faking it) > ><cfscript> >//making a fake query to turn into a tree > pResults = querynew("personid,supervisor_id"); > tmp = queryaddrow(pResults,7); > tmp = querysetcell(pResults,"personid","hdb4239",1); > tmp = querysetcell(pResults,"supervisor_id","dre8714",1); > tmp = querysetcell(pResults,"personid","jpk7656",2); > tmp = querysetcell(pResults,"supervisor_id","dre8714",2); > tmp = querysetcell(pResults,"personid","fxj6767",3); > tmp = querysetcell(pResults,"supervisor_id","dre8714",3); > tmp = querysetcell(pResults,"personid","adj2345",4); > tmp = querysetcell(pResults,"supervisor_id","hdb4239",4); > tmp = querysetcell(pResults,"personid","sgv1234",5); > tmp = querysetcell(pResults,"supervisor_id","hdb4239",5); > tmp = querysetcell(pResults,"personid","lat3456",6); > tmp = querysetcell(pResults,"supervisor_id","hdb4239",6); > tmp = querysetcell(pResults,"personid","ren9876",7); > tmp = querysetcell(pResults,"supervisor_id","lat3456",7); ></cfscript> > > >2. "functionalize" the cfquery functionality to allow me to use cfquery >from >within a cfscript function > ><cffunction name = "cfquery" returnType = "query" access = "private" output >= "yes" displayName = "cfquery"> ><cfargument name="SQLString" type="string" required="Yes"> > <cfquery dbtype="query" >name="theseresults">#preservesinglequotes(SQLString)#</cfquery> > <cfreturn theseresults> ></cffunction> > >3. Create my treemaker function ><cfscript> >function showtree(thiskey,level){ > writeoutput(repeatstring("-",level) & thiskey & "<br>"); > thislevel = level+1; > thisquery = cfquery("select * from pResults where supervisor_id = '" & >thiskey & "'"); > if(thisquery.recordcount gt 0){//this individual supervises someone... > for (i=1; i lte thisquery.recordcount; i=i+1){//loop through thiskey's >subordinates... > haskids = cfquery("select * from pResults where supervisor_id > = '" & >thisquery.personid[i] & "'"); > if(haskids.recordcount gt 0){//the subordinate of thiskey has >subordinates themselves, so feed their id to the showtree function... > showtree(thisquery.personid[i],thislevel); > } > else{//this subordinate of thiskey had no subordinates > themselves...just >output their value > writeoutput(repeatstring("-",thislevel) & > thisquery.personid[i] & >"<br>"); > } > } > } > return true; >}//end of function > ></cfscript> > >4. Call the function, feeding it a starting person id > ><cfset tmptree = showtree("dre8714",0)> > > >Okay, that's it. Now, here's what it's spitting out for me: > >dre8714 >-hdb4239 >--adj2345 >--sgv1234 >--lat3456 >---ren9876 > >These results are correct; However, even though it successfully drilled >down >to the innermost level and output with proper indentation, it refuses to >drill back up again and pick up where it left off in the function call >immediately above the most inner one (next line after the last one printed >should have been "-jpk7656"). I suspect it has something to do with the >correct usage of a "return", but I've put returns all over the place and >not >gotten any better output than what is copied above. > >I know this is such a simple thing and that one of my peers out there will >spot right away what the issue is. Thanks for taking a few minutes to lend >to my challenge. > >Doug :0) > >_________________________________________________________________ >FREE pop-up blocking with the new MSN Toolbar � get it now! >http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/ > > >========================================================= >Kansas City ColdFusion User Group's website & listserv is >hosted through the generous support of Clickdoug.com >To send email to the list, email [EMAIL PROTECTED] >To (un)subscribe, email [EMAIL PROTECTED] with your request. >For hosting solutions http://www.clickdoug.com >Featuring Win2003 Enterprise, RedHat Linux, CFMX 6.1. >====================================================== > > > >========================================================= >Kansas City ColdFusion User Group's website & listserv is >hosted through the generous support of Clickdoug.com >To send email to the list, email [EMAIL PROTECTED] >To (un)subscribe, email [EMAIL PROTECTED] with your request. >For hosting solutions http://www.clickdoug.com >Featuring Win2003 Enterprise, RedHat Linux, CFMX 6.1. >====================================================== _________________________________________________________________ Get tips for maintaining your PC, notebook accessories and reviews in Technology 101. http://special.msn.com/tech/technology101.armx ========================================================= Kansas City ColdFusion User Group's website & listserv is hosted through the generous support of Clickdoug.com To send email to the list, email [EMAIL PROTECTED] To (un)subscribe, email [EMAIL PROTECTED] with your request. For hosting solutions http://www.clickdoug.com Featuring Win2003 Enterprise, RedHat Linux, CFMX 6.1. ====================================================== ========================================================= Kansas City ColdFusion User Group's website & listserv is hosted through the generous support of Clickdoug.com To send email to the list, email [EMAIL PROTECTED] To (un)subscribe, email [EMAIL PROTECTED] with your request. For hosting solutions http://www.clickdoug.com Featuring Win2003 Enterprise, RedHat Linux, CFMX 6.1. ======================================================
