Page 1 of 1

Nested Categories - Recursion Using Components Rate Topic: ***** 1 Votes

#1 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1876
  • View blog
  • Posts: 20,284
  • Joined: 17-March 01

Posted 15 March 2006 - 02:18 PM

Just a quick little tutorial on how to create display nested categories from a database using recursion and components. This is the simplest way I've found to accomplish recursion in ColdFusion, but feel free to comment if you know a better way.

In this tutorial, we are working with a single table in a SQL database with 3 columns: id, parent, name.

Here is the database structure:

categories
-----------------------------------
id (int, auto-increment, primary key)
parent (int)
name (nvarchar)


Create the component

Create a new component called categories.cfc, start with this structure:

<cfcomponent name=&#34;Categories&#34;&#62;
<cffunction name=&#34;getCats&#34;&#62;

<!--- Parent ID ---&#62;
<cfargument name=&#34;parent&#34; type=&#34;numeric&#34;&#62;


</cffunction&#62;
</cfcomponent&#62;


As you may already know, recursion simply calls the same function from within itself. The beauty of using components is there is no need to number or name our function or objects because each call creates a new instance of the object.

Setup the query

In the code above, we've created a function called getCats, it accepts 1 argument: parent. We will use this argument to query for child categories.


<cfcomponent name=&#34;Categories&#34;&#62;
<cffunction name=&#34;getCats&#34;&#62;

<!--- Parent ID ---&#62;
<cfargument name=&#34;parent&#34; type=&#34;numeric&#34;&#62;

<!--- Query for Children of Parent ---&#62;
<cfquery name=&#34;qCats&#34; datasource=&#34;YOUR_DATASOURCE&#34;&#62;
SELECT * FROM categories WHERE parent = '#arguments.parent#'
</cfquery&#62;

</cffunction&#62;
</cfcomponent&#62;


Create the output

Now that we've setup a query to get all the nested (child) categories, we should setup the output. We'll do this by setting text to a output variable which will be returned. Remember, you should never need to display anything from inside your functions, it should be returned using cfreturn.


<cfcomponent name=&#34;Categories&#34;&#62;
<cffunction name=&#34;getCats&#34;&#62;

<!--- Parent ID ---&#62;
<cfargument name=&#34;parent&#34; type=&#34;numeric&#34;&#62;

<!--- Query for Children of Parent ---&#62;
<cfquery name=&#34;cats&#34; datasource=&#34;YOUR_DATASOURCE&#34;&#62;
SELECT * FROM categories WHERE parent = '#arguments.parent#'
</cfquery&#62;

<!--- Create Output ---&#62;
<cfset output = &#34;<ul&#62;&#34;&#62;

<!--- Loop over all items ---&#62;
<cfloop query=&#34;cats&#34;&#62;
<cfset output = &#34;#output# <li&#62;#cats.name#</li&#62;&#34;&#62;

<!--- Check Children of this item ---&#62;
<cfif cats.recordcount GT 0&#62;
<cfset output = &#34;#output# #CreateObject&#40;'component', 'categories'&#41;.getCats&#40;cats.id&#41;#&#34;&#62;
</cfif&#62;

</cfloop&#62;
<cfset output = &#34;#output#</ul&#62;&#34;&#62;

<cfreturn output /&#62;


</cffunction&#62;
</cfcomponent&#62;


We did a lot in that step, but it's really pretty simple. We've looped over the query appending text to our output variable and using #CreateObject('component', 'categories').getCats(cats.id)# we call the component and function from within itself to get child categories for the category the query is currently on. After the loop is finished, we return the output variable.

Putting it on a page

Save your categories.cfc and create a new .cfm file. This is where you'll call your recursive function and display your categories in a nicely organized list.


<cfoutput&#62;
#CreateObject&#40;'component', 'categories'&#41;.getCats&#40;0&#41;#
</cfoutput&#62;


We instantiate the categories component we just made and call the getCats function. When you created your table to query categories from, all of your root (top-level) categories should have a parent of 0, passing 0 to the parent argument starts at the very top of your nesting and begins to recurse down.

You should see something like this:


  • Parent 1
    • Child 1.1
      • Child 1.2
        • Child 1.2.1
          • Child 1.2.1.1
            • Child 1.2.1.1.1
            • Child 1.2.1.2
              • Child 1.2.1.3
                • Child 1.2.1.3.1
          • Parent 2


            Is This A Good Question/Topic? 0
            • +

            Replies To: Nested Categories - Recursion Using Components

            #2 sadara  Icon User is offline

            • New D.I.C Head

            Reputation: 0
            • View blog
            • Posts: 1
            • Joined: 02-July 06

            Posted 02 July 2006 - 06:00 AM

            thanks for this tutorial! after hours of banging my head against a brick wall, i have a working script.
            Was This Post Helpful? 0
            • +
            • -

            #3 billybrag  Icon User is offline

            • New D.I.C Head

            Reputation: 0
            • View blog
            • Posts: 2
            • Joined: 23-August 06

            Posted 23 August 2006 - 03:55 AM

            hello there,
            i tried this and it works ok, but produces lots of empty UL's

            please can you help me to sort them - an eg of this in action is here... www.organiclinker.com/test.cfm

            thanks
            Mike

            This post has been edited by billybrag: 23 August 2006 - 03:55 AM

            Was This Post Helpful? 0
            • +
            • -

            #4 skyhawk133  Icon User is offline

            • Head DIC Head
            • member icon

            Reputation: 1876
            • View blog
            • Posts: 20,284
            • Joined: 17-March 01

            Posted 23 August 2006 - 06:54 AM

            You may need to add some code in the output to check for an empty value. Not sure why it would be outputting empty UL's. Do you have anything in your database that might cause this?!
            Was This Post Helpful? 0
            • +
            • -

            #5 billybrag  Icon User is offline

            • New D.I.C Head

            Reputation: 0
            • View blog
            • Posts: 2
            • Joined: 23-August 06

            Posted 23 August 2006 - 07:48 AM

            View Postskyhawk133, on 23 Aug, 2006 - 06:54 AM, said:

            You may need to add some code in the output to check for an empty value. Not sure why it would be outputting empty UL's. Do you have anything in your database that might cause this?!


            No - not that i can see - all i have changed from your code is the variable names to match my db?

            it seems to be a problem with the logic?
            Was This Post Helpful? 0
            • +
            • -

            #6 monizzle  Icon User is offline

            • New D.I.C Head

            Reputation: 0
            • View blog
            • Posts: 1
            • Joined: 15-December 06

            Posted 15 December 2006 - 10:33 AM

            I made a few changes to skyhawk133's code so that it produces valid html. (see this url for an explanation of proper list nesting: http://www.w3schools...xhtml_html.asp)

            In skyhawk's original code the child ul's were not nested within the parent li's. There may be a more elegant way to do what I have done, so please chime in if you've got one.

            skyhawk133: Thanks for the original post!

            
            <!--- Create Output --->
            <!--- Monizzle: The following IF wrapper makes sure an empty <ul> isn't returned if no children are found with the default parent id  --->
              <cfif qCats.recordcount GT 0> 
              <cfset output = "<ul>">
              
              <!--- Loop over all items --->
              <cfloop query="cats">
            	<!--- Monizzle: Removed closing </li> from output below to properly nest child <ul>s  --->
            	  <cfset output = "#output# <li>#cats.name#">
            	  
            	<!--- Check Children of this item --->
            	<cfif cats.recordcount GT 0>
            		<cfset output = "#output# #CreateObject('component', 'categories').getCats(cats.id)#">
            	</cfif>
            	<!--- Monizzle: Add closing </li> to the output  ---> 		
            	<cfset output = "#output#</li>">
              </cfloop>
              <cfset output = "#output#</ul>">
              
              <cfreturn output />
            <!--- Monizzle: close IF wrapper --->
            </cfif>
            
            
            

            Was This Post Helpful? 0
            • +
            • -

            #7 dduck1934  Icon User is offline

            • New D.I.C Head

            Reputation: 0
            • View blog
            • Posts: 3
            • Joined: 15-September 06

            Posted 13 April 2008 - 06:09 PM

            instead of Creating a new Object everytime, couldnt you change this line..

            <cfset output = "#output# #CreateObject('component', 'categories').getCats(cats.id)#">


            to just <cfset output = "#output# #getCats(cats.id)#">
            Was This Post Helpful? 0
            • +
            • -

            #8 Guest_Misty*


            Reputation:

            Posted 06 May 2010 - 09:15 AM

            View Postdduck1934, on 13 April 2008 - 05:09 PM, said:

            instead of Creating a new Object everytime, couldnt you change this line..

            <cfset output = "#output# #CreateObject('component', 'categories').getCats(cats.id)#">


            to just <cfset output = "#output# #getCats(cats.id)#">


            Weel Its quite late i am making a reply here, but even using the updated code, i still get empty records and only one of my category display nothing else:

            Here is what i am trying to do!

            <cffunction name="getCats" returntype="any">
              <cfargument name="parentid" type="numeric" default="0">
              <cfquery name="qCats" datasource="#request.dsn#" username="#request.user#" password="#request.pass#">
              		SELECT * FROM categories 
                    WHERE parentID = <cfqueryparam cfsqltype="cf_sql_numeric" value="#arguments.parentid#">
              		</cfquery>
              <cfif qCats.recordcount GT 0>       
              <cfset output = "<ul>">
              <cfloop query="qCats">
                <cfset output = "#output# <li>#qCats.Category#">
                <cfif qCats.recordcount GT 0>
                  <cfset output = "#output# #getCats(qcats.catID)#">  
                </cfif>
                <cfset output = "#output#</li>">
              </cfloop>
              <cfset output = "#output#</ul>">
              <cfreturn output>
              </cfif>
            </cffunction>
            
            
            


            Calling it like This:

            <cfset tools = CreateObject('component', '#cfcPath#.categories')>
            <cfset getAll = #tools.getCats(parentID=0)#>
            <cfoutput>#getAll#</cfoutput>
            
            


            it is showing like this:

            <ul> <li>Business <ul> <li>Directories </li> <li></li></ul></li> <li></li> <li></li> <li></li> <li></li> <li></li> <li></li> <li></li> <li></li> <li></li></ul>
            
            

            Was This Post Helpful? 1

            #9 ravi bhalgami  Icon User is offline

            • New D.I.C Head

            Reputation: 0
            • View blog
            • Posts: 1
            • Joined: 22-April 11

            Posted 22 April 2011 - 04:42 AM

            View PostMisty, on 06 May 2010 - 09:15 AM, said:

            View Postdduck1934, on 13 April 2008 - 05:09 PM, said:

            instead of Creating a new Object everytime, couldnt you change this line..

            <cfset output = "#output# #CreateObject('component', 'categories').getCats(cats.id)#">


            to just <cfset output = "#output# #getCats(cats.id)#">


            Weel Its quite late i am making a reply here, but even using the updated code, i still get empty records and only one of my category display nothing else:

            Here is what i am trying to do!

            <cffunction name="getCats" returntype="any">
              <cfargument name="parentid" type="numeric" default="0">
              <cfquery name="qCats" datasource="#request.dsn#" username="#request.user#" password="#request.pass#">
              		SELECT * FROM categories 
                    WHERE parentID = <cfqueryparam cfsqltype="cf_sql_numeric" value="#arguments.parentid#">
              		</cfquery>
              <cfif qCats.recordcount GT 0>       
              <cfset output = "<ul>">
              <cfloop query="qCats">
                <cfset output = "#output# <li>#qCats.Category#">
                <cfif qCats.recordcount GT 0>
                  <cfset output = "#output# #getCats(qcats.catID)#">  
                </cfif>
                <cfset output = "#output#</li>">
              </cfloop>
              <cfset output = "#output#</ul>">
              <cfreturn output>
              </cfif>
            </cffunction>
            
            
            


            Calling it like This:

            <cfset tools = CreateObject('component', '#cfcPath#.categories')>
            <cfset getAll = #tools.getCats(parentID=0)#>
            <cfoutput>#getAll#</cfoutput>
            
            


            it is showing like this:

            <ul> <li>Business <ul> <li>Directories </li> <li></li></ul></li> <li></li> <li></li> <li></li> <li></li> <li></li> <li></li> <li></li> <li></li> <li></li></ul>
            
            

            Was This Post Helpful? 0
            • +
            • -

            Page 1 of 1