20 Replies Latest reply on Sep 1, 2009 9:23 AM by semi star gazer

    Hi Guys, a Recusion is Cutting me Off

    newchickinCF Level 1
      I have a PHP code which uses recursion to get categories and subcategories.

      I am trying to convert it with coldfusion but getting into trouble:

      here is the code:

      $result = mysql_query("select title,parentid from cat where cid=$catid");
      list($title,$parentid)=mysql_fetch_row($result);
      $title=getparentlink($parentid,$title);
        • 1. Re: Hi Guys, a Recusion is Cutting me Off
          Dan Bracuk Level 5
          The first line runs a query.
          The second converts the query to a list.
          The third one runs a function that appears to run a query for the parentid.

          If this is recursion, this code should be in a loop.
          • 2. Re: Hi Guys, a Recusion is Cutting me Off
            Level 7
            > I have a PHP code which uses recursion to get categories and subcategories.
            >
            > I am trying to convert it with coldfusion but getting into trouble:
            >

            Could you possibly post the code you're having trouble with, and explain
            what the trouble actually is?

            --
            Adam
            • 3. Hi Guys, a Recusion is Cutting me Off
              newchickinCF Level 1
              Hi Guys, Here is the Code, I am trying to Do:

              i get query results as: select * from cat where parentid = 0

              now i use this query as:

              <cfoutput query="results">
              <cfquery datasource="#request.dsn#" name="a">
              select catID, category from
              categories
              where
              parentid = <cfqueryparam cfsqltype="cf_sql_numeric" value="#Trim(results.catID)#">
              </cfquery>
              <td height="30" valign="middle"><strong><a href="index.cfm?catID=#URLEncodedFormat(catID)#">#category#</a></strong><br>
              <cfloop query="a">
              <cfquery datasource="#request.dsn#" name="showanother">
              select * from table
              where catID=<cfqueryparam cfsqltype="cf_sql_numeric" value="#Trim(a.catID)#">
              </cfquery>
              <a href="index.cfm?catID=#URLEncodedFormat(catID)#">#Category#</a>
              </cfloop>
              </cfoutptut>

              Bur it is appearing like:

              Home: CategoryName

              and if i select another category it is appearing as:

              Home: SubCategoryName

              But I want Something like this:

              Home: CategoryName: SubCategoryName

              Hope u understand what i am trying to do here:

              Regards
              • 4. Re: Hi Guys, a Recusion is Cutting me Off
                Dan Bracuk Level 5
                You are running way too many queries. You only need one as far as I can see.

                select yourfields
                from cat c join category ca on c.catid = ca.catid
                join table t on ca.catid = t.catid
                order by categoryname, subcategoryname

                Then use the group attibute of cfoutput to organize your display.
                • 5. Re: Hi Guys, a Recusion is Cutting me Off
                  Level 7
                  OK the problem here is that you're not actually doing anything recursively,
                  you're just looping over a query.

                  What you need to do is to write a function that takes an ID which queries
                  for either the descendants or ancestors depending on what you're trying to
                  do; and for each of those, call the same function again with that record's
                  ID as the ID you pass in. That will have you traversing into or out of the
                  hierarchy. What you do as you traverse is up to you: looks like you need to
                  build up a recordset of IDs & category labels, which ultimately gets passed
                  back to the calling code.

                  Two things:
                  * if you're doing this, you need to read up on how recursion works, because
                  you don't seem to be getting it. There's plenty of stuff around that
                  Google will dig up for you.
                  * the ID / parent-ID (adjacency model) method of doing hierarchies is a
                  pretty poor performer, specifically because one needs to do all this
                  recursion. I recommend you look up "nested set", and re-implement your
                  hierarchy using that model, if possible.

                  --
                  Adam
                  • 6. Re: Hi Guys, a Recusion is Cutting me Off
                    newchickinCF Level 1
                    Thanks, Pointing me out that i am not doing anything recursive. well I used queries inside a query to get count the records of categories and subcategories undeneath it..

                    but how i display it like a breadcrumb on main link.

                    Please show me a point Guys.

                    Cheers
                    • 7. Re: Hi Guys, a Recusion is Cutting me Off
                      Dan Bracuk Level 5
                      A single query with query of queries to get the counts would be much more efficient than using queries inside a query.

                      As far as the breadcrumb goes, what have you accomplished so far, and what is it you want to display?
                      • 8. Re: Hi Guys, a Recusion is Cutting me Off
                        newchickinCF Level 1
                        I have worked such as:

                        Home: CategoryName

                        and if i select subcategory it replace the above as:

                        Home: Subcatname

                        but i want it to appear it as:

                        Home: categoryName: subcatName

                        • 9. Re: Hi Guys, a Recusion is Cutting me Off
                          Dan Bracuk Level 5
                          What is the code that generates those displays?
                          • 10. Re: Hi Guys, a Recusion is Cutting me Off
                            Level 7
                            > Thanks, Pointing me out that i am not doing anything recursive. well I used
                            > queries inside a query to get count the records of categories and subcategories
                            > undeneath it..
                            >
                            > but how i display it like a breadcrumb on main link.
                            >
                            > Please show me a point Guys.

                            Well I kinda wish you'd followed my pointer to "read up on recursion", and
                            try working it out for yourself. That's the best way to learn.

                            You could at leats have used Google to search for "breadcrumbs recursive
                            coldfusion" (which yields some results). Or *something*.


                            However I find myself in an uncharacteristically helpful (if not polite.
                            But then I'm rarely polite) mood tonight, so here's some sample code.

                            <cfscript>
                            // this is in lieu of a DB table
                            qData = queryNew("catId,parentId,label");
                            queryAddRow(qData); qData.catId[1] = 1; qData.parentId[1] = "";
                            qData.label[1] = "My Site";
                            queryAddRow(qData); qData.catId[2] = 2; qData.parentId[2] = 1;
                            qData.label[2] = "News";
                            queryAddRow(qData); qData.catId[3] = 3; qData.parentId[3] = 1;
                            qData.label[3] = "Products";
                            queryAddRow(qData); qData.catId[4] = 4; qData.parentId[4] = 1;
                            qData.label[4] = "Support";

                            queryAddRow(qData); qData.catId[5] = 5; qData.parentId[5] = 3;
                            qData.label[5] = "Software";
                            queryAddRow(qData); qData.catId[6] = 6; qData.parentId[6] = 3;
                            qData.label[6] = "Hardware";

                            queryAddRow(qData); qData.catId[7] = 7; qData.parentId[7] = 6;
                            qData.label[7] = "PCs";
                            queryAddRow(qData); qData.catId[8] = 8; qData.parentId[8] = 6;
                            qData.label[8] = "Macs";
                            </cfscript>


                            <!--- say we're on the Macs page --->
                            <cfset pageId = 8>

                            <cfset lBreadcrumbs = getBreadcrumbs(id=pageId)>
                            <cfoutput>#listChangeDelims(lBreadcrumbs, " &rsaquo; ")#</cfoutput>


                            <cffunction name="getBreadcrumbs" returntype="string" output="false">
                            <cfargument name="id" type="numeric" required="true">

                            <cfset var qThis = "">
                            <cfset var lReturn = "">
                            <!--- get the record for this page --->
                            <cfquery name="qThis" dbtype="query" maxrows="1">
                            select parentId, label
                            from qData
                            where catId = <cfqueryparam value="#arguments.id#"
                            cfsqltype="CF_SQL_INTEGER">
                            </cfquery>
                            <cfif len(qThis.parentId[1])><!--- if it's got a parent, go grab it --->
                            <cfset lReturn = getBreadcrumbs(id=qThis.parentId[1])><!--- THIS IS THE
                            RECURSIVE BIT --->
                            </cfif>
                            <!--- once we've grabbed the parent, append this node's label, and we will
                            have the labels for the parent, followed by itself. Return that. --->
                            <cfreturn listAppend(lReturn, qThis.label[1])>
                            </cffunction>

                            It was a rush job, but it shows how to use recursion to extract breadcrumbs
                            from an adjacency list. And I was rather pleased it worked on first pass.
                            Not even any typos!

                            Make an effort to understand it, and report back with any questions. By
                            far the easiest way of understanding recursive stuff like this is to use
                            pencil and paper to keep track of the values being passed in and out of the
                            function, and watch what happens to them. Really: pencil and paper.

                            --
                            Adam
                            • 11. Hi Guys, a Recusion is Cutting me Off
                              newchickinCF Level 1
                              Hi Thanks!!!

                              I tried my code to be bit easy. and remove some contents in now i now i am simply using this to make try the breadcrumsbut it is not working: i think i am missing something big here:

                              Here is query very simple instead.

                              select * from cat where parentid = 0


                              <cfoutput query="myResult">
                              <table cellpadding="5" border="0" width="100%">
                              <tr align="center">
                              <td valign="middle" align="left"><p align="left"> <strong><a href="index.cfm">Home</a></strong>  <strong><big>&raquo;</big> #Category# (#subcat.total#)</strong></p></td>
                              </tr>
                              <tr>
                              <td>All Users</td>
                              </tr>
                              </table>
                              </cfoutput>

                              well this is my subcat query :

                              SELECT count(ID) AS total, count(cat.catID) as totalcat
                              FROM table1 LEFT JOIN cat ON table1.catID = cat.catID
                              WHERE table1.catID = cat.catID
                              AND cat.parentid= <cfqueryparam cfsqltype="cf_sql_numeric" value="#trim(arguments.catID)#">
                              or cat.catID = <cfqueryparam cfsqltype="cf_sql_numeric" value="#trim(arguments.catID)#">


                              so it works simple way:

                              home: categoryName

                              and then i choose subcat:

                              home:subcatName
                              • 12. Re: Hi Guys, a Recusion is Cutting me Off
                                Level 7
                                > I tried my code to be bit easy. and remove some contents in now i now i am
                                > simply using this to make try the breadcrumsbut it is not working: i think i am
                                > missing something big here:

                                Yes you are.

                                Your code will always ever only display "home > PARENT-OF-CURRENT-NODE >
                                CURRENT-NODE", because you're only ever doing one lookup: getting the
                                parent of the node. To get all the generations from the child up the tree
                                back to the top (eg: Home > GREAT-GRANDPARENT > GRANPARENT > PARENT >
                                NODE), you NEED to do the recursion, so that you look up each preceding
                                generation. The code I gave you is about as simple as you can make it,
                                with it fulfilling the complete requirement.

                                --
                                Adam
                                • 13. Hi Guys, a Recusion is Cutting me Off
                                  newchickinCF Level 1
                                  Hi, I tried your way:

                                  as i did some changes in my code: used asquery as:

                                  <cffunction name="showcats" returntype="string" output="false">
                                  <cfargument name="catid" type="numeric" required="true">
                                  <cfargument name="recurse" type="any" required="no">
                                  <cfset var qThis = "">
                                  <cfset var lReturn = "">
                                  <cfquery name="qThis" datasource="#request.dsn#">
                                  select * from categories
                                  where catId = <cfqueryparam value="#arguments.catid#" cfsqltype="CF_SQL_INTEGER">
                                  </cfquery>
                                  <cfif len(qThis.parentId[1])>
                                  <cfset lReturn = showcats(catid=qThis.parentId[1])>
                                  </cfif>
                                  <cfreturn listAppend(lReturn, qThis.category[1])>
                                  </cffunction>

                                  then on my page is use something like thsi:

                                  <cfinvoke component="core.cfc.show" method="showcats" recurse="1" catID="#url.catID#" returnvariable="myResult"/>
                                  <cfoutput query="myResult">
                                  <table cellpadding="5" border="0" width="100%">
                                  <tr align="center">
                                  <td valign="middle" align="left"><p align="left">
                                  <strong><a href="index.cfm">Home</a></strong> 
                                  <cfset lBreadcrumbs = myResult(catid=pageId)>
                                  <cfoutput>#listChangeDelims(lBreadcrumbs, " &rsaquo; ")#</cfoutput> </p>
                                  <!---<strong><big>&raquo;</big> #Category# (#subcat.total#)</strong></p>--->
                                  </td>
                                  </tr>
                                  </table></cfoutput>
                                  but it is not displaying:

                                  it is providing me an error of:

                                  Complex object types cannot be converted to simple values.
                                  The expression has requested a variable or an intermediate expression result as a simple value, however, the result cannot be converted to a simple value. Simple values are strings, numbers, boolean values, and date/time values. Queries, arrays, and COM objects are examples of complex values.

                                  The most likely cause of the error is that you are trying to use a complex value as a simple one. For example, you might be trying to use a query variable in a cfif tag.

                                  The error occurred in C:\Inetpub\wwwroot\projects\core\cfc\show.cfc: line 46
                                  Called from C:\Inetpub\wwwroot\projects\showall.cfm: line 5
                                  Called from C:\Inetpub\wwwroot\projects\index.cfm: line 128
                                  Called from C:\Inetpub\wwwroot\projects\index.cfm: line 1
                                  44 : <cfset lReturn = showcats(catid=myResult.parentId[1])>
                                  45 : </cfif>
                                  46 : <cfreturn listAppend(lReturn, myResult.category[1])>
                                  47 : <cfelse>
                                  48 : <cfreturn myResult>
                                  • 14. Re: Hi Guys, a Recusion is Cutting me Off
                                    Level 7
                                    > <cffunction name="showcats" returntype="string" output="false">

                                    [...]
                                    > <cfinvoke component="core.cfc.show" method="showcats" recurse="1"
                                    > catID="#url.catID#" returnvariable="myResult"/>
                                    > <cfoutput query="myResult">

                                    showcats() returns a string. You're trying to treat it as a query.


                                    > <cfset lBreadcrumbs = myResult(catid=pageId)>

                                    And here you're treating it like it's a function!

                                    I think you need to pay slightly closer attention to what you're doing.

                                    --
                                    Adam
                                    • 15. Re: Hi Guys, a Recusion is Cutting me Off
                                      newchickinCF Level 1
                                      Cool! Thanks I go it working..

                                      Only Thing occuring is when it creates a node like:

                                      home > category > subcategory > subsubcategory

                                      how i can link the previous parentID like:

                                      home > category > subcategory > subsubcategory

                                      i mean wanna link the category, sub category so when i click on the category and subcategory i should be able to go back..

                                      is some way to get this working

                                      Regards
                                      • 16. Re: Hi Guys, a Recusion is Cutting me Off
                                        Level 7
                                        > how i can link the previous parentID like:
                                        >
                                        > home > category > subcategory > subsubcategory
                                        >
                                        > i mean wanna link the category, sub category so when i click on the category
                                        > and subcategory i should be able to go back..
                                        >
                                        > is some way to get this working


                                        Yes, there is. I think the time has come for you to try to get it sorted
                                        YOURSELF, without referring back here.

                                        Try to come up with a solution, and if you have problems refer back here.
                                        Don't ask us to do your work for you in the first instance.

                                        --
                                        Adam
                                        • 18. Re: Hi Guys, a Recusion is Cutting me Off
                                          semi star gazer

                                          Hi, I know this post is a little old, but I am having some trouble making the code work, and I would appreciate any advice! Right now, I am using the following function to create the list of breadcrumbs:

                                           

                                          <!--- generate breadcrumbs --->
                                              <cffunction name="getBreadcrumbs" returntype="string" access="public" output="false">
                                                  <cfargument name="pageID" required="yes" type="numeric">
                                                 
                                                  <cfset var vBreadcrumbs = '' />
                                                 
                                                  <cfquery name="qGetPageInfo" datasource="#application.db#" maxrows="1">
                                                      SELECT
                                                          *
                                                      FROM
                                                          admin_navigation
                                                      WHERE
                                                          id = <cfqueryparam value="#arguments.pageID#" cfsqltype="cf_sql_integer">
                                                  </cfquery>
                                                 
                                                  <!--- 0 is a top level item --->

                                                  <cfif qGetPageInfo.parentID NEQ 0>
                                                      <cfset vBreadcrumbs = getBreadcrumbs(qGetPageInfo.parentID) />
                                                  </cfif>

                                           

                                                  <cfset vBreadcrumbs = listAppend(vBreadcrumbs, qGetPageInfo.pageName) />
                                                 
                                                  <cfreturn vBreadcrumbs />
                                              </cffunction>

                                           

                                          This is being used to display the breadcrumb function:

                                           

                                          <cfset getBreadcrumbsQuery = application.adminObject.getBreadcrumbs(pageID) />
                                          #listChangeDelims(getBreadcrumbsQuery, " &raquo; ")#

                                           

                                          However, what I get is a list with the correct number of levels, but the page name that is displayed is always the top-level breadcrumb that should be called. For example, I should see Courses >> Core Course Management for the page I am testing on, and instead I just get Courses >> Courses.

                                           

                                          I would appreciate any pointers in the right direction! Thanks.

                                           

                                          KC

                                          • 19. Re: Hi Guys, a Recusion is Cutting me Off
                                            Adam Cameron. Level 5

                                            You shouldn't really hijack someone else's thread (even a long dead one) with your own question.  Etiquette is generally "one question per thread".

                                             

                                            First things first, you need to VAR all your variables in your function.  This is especially critical when recursing, because otherwise each new call will overwrite the variables from the previous call.

                                             

                                            Get that sorted out first.

                                             

                                            Second - and this is unrelated to your immediate issue, but it's just poor form - never use SELECT * unless you actually want all columns in the result set.  Even then, it's still better to list all the columns.  For your purposes here, you only need a couple of columns, so only fetch those columns.

                                             

                                            Third - the adjacency list model  (ID / PARENT_ID) for representing a hierarchy is not very performant, and does not scale.  I recommend using the nested set model instead.

                                             

                                            Ooh... fourth... what DB system are you using?  Depending on your answer you can probably dispense with the recursion and get the DB to get the results with a single query.

                                             

                                             

                                            --

                                            Adam