xeondxb
asked on
Coldfusion recursive categories fetching loop from Database!
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...
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>
db.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 parent.
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,
as
(
Select CatID,ParentID,Catname,0 as lev,
convert(varchar(100),Catna
FROM yourtable WITH (NOLOCK)
Where ParentID = 0
UNION ALL
Select c.CatID,c.ParentID,c.Catna
convert(varchar(50),r.Orde
FROM yourtable c WITH (NOLOCK)
inner join catrecurse r on r.CatID = c.ParentID
)
Select CatID,ParentID,Catname,lev
From catrecurse WITH (NOLOCK)
Order by Orderby