?
Solved

Coldfusion recursive categories fetching loop from Database!

Posted on 2014-03-17
2
Medium Priority
?
261 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses
Course of the Month10 days, 22 hours left to enroll

770 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