Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Coldfusion recursive categories fetching loop from Database!

Posted on 2014-03-17
2
Medium Priority
?
264 Views
Last Modified: 2014-03-18
Hi guys,

im trying to fetch my categories from Database. This is what im doing  but im not happy because what if my category goes one more level up. What will happen then i have to  write a code again. My code is also very lengthy.
I  believe i we can do in one or two loops ...
 
Please help me to write good clean code and more efficient...


<cffunction name="categorySequenceBuilder" output="false" access="private" returntype="any" hint="Return the Child of parent Category">
	<cfset var getAllCategoriesFromDb = getAllCategories()>
	<cfset var countCatRecords = #int(categoriesTotalCount)#>
	<cfset var rCount= 1>
			
	<cfset var RRCategory = "">
	
		<!---Building New Query --->
		<cfset RRCategory = QueryNew("ID, Name, Parent", "Integer,VarChar,Integer")>
		<cfset newRow = QueryAddRow(RRCategory, #int(countCatRecords)#)>
		
		
			<cfloop query="getAllCategoriesFromDb">
				
				<cfif #int(rCount)# LTE #int(countCatRecords)#>
						<cfset temp = QuerySetCell(RRCategory, "id",#getAllCategoriesFromDb.id# , #rCount#)>
						<cfset temp = QuerySetCell(RRCategory, "Name",#getAllCategoriesFromDb.Name# , #rCount#)>
						<cfset temp = QuerySetCell(RRCategory, "parent",#getAllCategoriesFromDb.parent# , #rCount#)>
						<cfset var rCount= rCount+1>
	
	
	
	
					<!---Checking Childs in Database --->
				<!---#2nd Level --->		
						<cfif #isChild(getAllCategoriesFromDb.id)# IS true>
							<cfset getParentQry2nd = CategoryChildFinder(#getAllCategoriesFromDb.id#)>
							<cfloop query="getParentQry2nd" >
								<cfset temp = QuerySetCell(RRCategory, "id",#getParentQry2nd.id# , #rCount#)>
								<cfset temp = QuerySetCell(RRCategory, "Name",#getParentQry2nd.Name# , #rCount#)>
								<cfset temp = QuerySetCell(RRCategory, "parent",#getParentQry2nd.parent# , #rCount#)>
								<cfset var rCount= rCount+1>
			
								<!--- #3rd Level --->
									
										
									<cfif #isChild(getParentQry2nd.id)# IS true>
								
										<cfset getParentQry3rd = CategoryChildFinder(#getParentQry2nd.id#)>
											<cfloop query="getParentQry3rd" >
												<cfset temp = QuerySetCell(RRCategory, "id",#getParentQry3rd.id# , #rCount#)>
												<cfset temp = QuerySetCell(RRCategory, "Name",#getParentQry3rd.Name# , #rCount#)>
												<cfset temp = QuerySetCell(RRCategory, "parent",#getParentQry3rd.parent# , #rCount#)>
												<cfset var rCount= rCount+1>
										    <!--- #4th  Level --->
												<cfif #isChild(getParentQry3rd.id)# IS true>
													<cfset getParentQry4th = CategoryChildFinder(#getParentQry3rd.id#)>
														<cfloop query="getParentQry4th" >
															<cfset temp = QuerySetCell(RRCategory, "id",#getParentQry4th.id# , #rCount#)>
															<cfset temp = QuerySetCell(RRCategory, "Name",#getParentQry4th.Name# , #rCount#)>
															<cfset temp = QuerySetCell(RRCategory, "parent",#getParentQry4th.parent# , #rCount#)>
															<cfset var rCount= rCount+1>
														</cfloop>
                                                </cfif>
											<!---#4th  Level End --->
										    </cfloop>
									</cfif>
							<!---#3rd Level End --->	
						    </cfloop>	
						</cfif>
				<!---#2nd Level End --->
				</cfif>
			</cfloop>
            <cfreturn RRCategory>

 </cffunction>

Open in new window

db.jpg
0
Comment
Question by:xeondxb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 25

Expert Comment

by:dgrafx
ID: 39938119
First look at attached image
You'll see top level Parent Categories who have a ParentID of 0 and then child categories whose parent is that of the same highlighted color top level parentparent / child recursion .
Note that I realized that this table doesn't have several levels of parent / child - but never fear because the code will accommodate.

For proof of concept just modify query to your column names.
You only need 3 columns - CatID - ParentID - Catname and of course change your tablename.
Note again that for this to work "right out of the box" you need a numeric CatID and a numeric ParentID and top level categories need a ParentID of 0 (zero) and an alphanumeric Catname

Here is the SQL
=============================================================

WITH catrecurse(CatID,ParentID,Catname,lev,Orderby)
      as
      (
            Select CatID,ParentID,Catname,0 as lev,                  
            convert(varchar(100),Catname) AS Orderby
            FROM yourtable WITH (NOLOCK)
            Where ParentID = 0      
                                          
      UNION ALL
      
            Select c.CatID,c.ParentID,c.Catname,r.lev + 1,
            convert(varchar(50),r.Orderby) + convert(varchar(50),c.Catname) AS Orderby
            FROM yourtable c WITH (NOLOCK)
            inner join catrecurse r on r.CatID = c.ParentID                              
      )
      
      Select CatID,ParentID,Catname,lev,Orderby
      From catrecurse WITH (NOLOCK)
      Order by Orderby
0
 
LVL 4

Accepted Solution

by:
onlyamir007 earned 2000 total points
ID: 39938864
Try This....

	<!--- init --->
    <cffunction name="init" output="false" access="public" returntype="any" hint="Constructor">
		
		<cfset  rCount=1>
    	<cfreturn this>
    </cffunction>

Open in new window



<cffunction name="categorySequenceBuilder" output="false" access="private" returntype="any" hint="Return the Child of parent Category">

	 
	<cfset var RRCategory = "">
	<cfset var getAllCategoriesFromDb = getAllCategories()>
	<cfset var countCatRecords = #int(categoriesTotalCount)#>
	
		<!---Building New Query --->
		<cfset RRCategory = QueryNew("ID, Name, Parent", "Integer,VarChar,Integer")>
		<cfset newRow = QueryAddRow(RRCategory, #int(countCatRecords)#)>
		
		
<!---Functions Building Category Query --->
			<cffunction name="buildQueryNodes"  returntype="void" access="public" >
			<cfargument name="queryObj"  type="query" >

				<cfloop query="#arguments.queryObj#" >
                
               
                <cfif  #int(rCount)# LTE countCatRecordS>
                
					<cfset temp = QuerySetCell(RRCategory, "id",#id# , #rCount#)>
					<cfset temp = QuerySetCell(RRCategory, "Name",#Name# , #rCount#)>
					<cfset temp = QuerySetCell(RRCategory, "parent",#parent# , #rCount#)>
					<cfset  rCount++>
                 
					<cfif #isChild(id)# IS true >
						<cfset getParentQry = CategoryChildFinder(#id#)>
						<cfscript>
								buildQueryNodes(getParentQry);
						</cfscript>
					</cfif>

                 
                 </cfif>
                 
				</cfloop>

			</cffunction>
		<!---End --->
		
		
		
			<cfloop query="getAllCategoriesFromDb">
				
				<cfif #int(rCount)# LTE #int(countCatRecords)#>
                 
                
						<cfset temp = QuerySetCell(RRCategory, "id",#getAllCategoriesFromDb.id# , #rCount#)>
						<cfset temp = QuerySetCell(RRCategory, "Name",#getAllCategoriesFromDb.Name# , #rCount#)>
						<cfset temp = QuerySetCell(RRCategory, "parent",#getAllCategoriesFromDb.parent# , #rCount#)>
						<cfset   rCount++>
	
	
					<!---Checking Childs in Database --->
				<!---#2nd Level --->		
						<cfif #isChild(id)# IS true >
							<cfset getParentQry = CategoryChildFinder(#id#)>
								<cfscript>
									buildQueryNodes(getParentQry);
								</cfscript>
						</cfif>
				<!---#2nd Level End --->
			     </cfif>
			</cfloop>
	       <cfreturn RRCategory>

 </cffunction>

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Suggested Courses

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question