Coldfusion Open Source: Custom tag to draw parent-child tree for an hierarchical table. -by CF-WORKS

Freelance Web Developer. ColdFusion, PHP, ASP.net programming worldwide

support@cf-works.com. SMS: +7-911-715-57-84

Custom tag to draw parent-child tree for an hierarchical table. (Item ID:161)

coldfusion custom tag, hierarchical table, date tree, parent-child relationship, child branches, parent rows, child rows, nested categories, example, parent-child

Type:	SOFTWARE (downloadable)
Category:	Coldfusion Open Source
Version:	1.4 build 7212 update 07/04/2010
Developer:	CF-Works [more items by CF-Works]
Seller:	CF-Works [more items from CF-Works]
Price:	$3.00
Availability:	Downloadable Immediately
Popularity:

This is essential coldfusion recursive custom tag returning all child branches for current one for an hierarchical table. By Hierarchical Table, I mean here a way of organization of data inside of sql table where rows have parent ones in the same table, within one-to-many relationship (this data model is also known as parent-child relationship):

CATEGORY CATEGORYID PARENTID

Web 15 0

Web Development and Design 16 15

Coldfusion 17 16

PHP 18 16

ASP.net 19 16

This is a nice way to store data but it may be a problem when you need to retrieve hierarchical tree from stored rows. This is the goal of the Get Child Branches custom tag.

Accepting Parameters:

table - table name (for the example above, "Category")

idfield - name of the ID field (here, "Categoryid")

namefield - name of the char name value field ("Category")

subidfield - name of the field which points to the parent branch ("Parentid")

branchid - starting branch id to draw tree from (default = "" - top)

So to get tree from the example table it would run like this:

<CF_GETANYBABY
��table="category"
��namefield="category"
��idfield="categoryid"
��subidfield="subcatofid"
��branchid=0 >

what returns 3 lists with the same length delimited by "|"

idbabylist - list of IDs (for the example: "15|16|19|17|18")

namebabylist - list of names ("web|web development and design|ASP.net|coldfusion|PHP")

levelbabylist - nesting levels list ("0|1|2|2|2)

So that is easy do draw tree accordingly entries sort order and nested depth:

Web
Web Development and Design

ASP.net

Coldfusion

PHP

BRIEF DESCRIPTION: Coldfusion recursive custom tag returning all child branches for current one for an hierarchical table (parent-child relationship). Parent-child relationship within the same table

Add your review to this item:

Existing customers please login (click "Your Account" top menu item).
New customers choose Quick Registration including just your name, email and password or Complete Registration

Q.:

What do I do with the three lists once they are created?

A.:

Once we have idbabylist and levelbabylist created, it is possible to update catorder and catdepth fields in the category table which mean "item sort order" and "item depth":

<CFSET npp = 1 >
    <CFLOOP list="#idbabylist#" index="i">
        <CFQUERY datasource="#REQUEST.ds#">
            update category set catorder=#npp#, catdepth=#listgetat(levelbabylist, npp)#
            where categoryid=#i#
        </CFQUERY>
    <CFSET npp=npp+1>
</CFLOOP>

It works like this: you call GetAnyBaby as often as you update your category tree. Maybe once a week or once a month or on demand only (the last is the best). And then to draw select with nested categories you don't need to call GAB script every time, just run single select to get categories depth and sort order.

Q.:

I just purchased your "Custom tag to draw parent-child tree for an hierarchical table". It works great, but do you have an example of how I can take the three lists your tag returns and use that to display the hierarchical menu?

A.:

hi Craig,
thank you for purchasing my custom tag,
I use it for updating category depth and sort order fileds 
(named "catdepth" and "catorder" in example below):

<CF_GETANYBABY
    table="category"
    namefield="category"
    idfield="categoryid"
    subidfield="subcatofid"  
    branchid=0>

<CFSET categoryids=idbabylist>

<CFSET npp =1>

<CFLOOP list="#categoryids#" index="i">
	<CFQUERY datasource="#REQUEST.ds#">
		update category set catorder = #npp#, catdepth=#listgetat(levelbabylist, npp)# 
		where categoryid=#i#
	</CFQUERY>
	<CFSET npp=npp+1>

</CFLOOP>

Then say we need to show some dropdown where categories and subcategories are displayed 
accordingly their depth and sort order:

<CFQUERY NAME="allcategories" datasource="replacementremotes">
	SELECT categoryid,category,catdepth, itemscount 
	FROM category where inactive=0 
	ORDER BY catorder
</CFQUERY>

<TR>
<TD>Category</TD>
<TD><INPUT TYPE="text" NAME="category" SIZE="40" VALUE="#category#"></TD>
</TR>
<TR>
<TD>Subcategory Of</TD>
<TD><SELECT NAME="subcatofid">
<OPTION value="0" selected>---------------------------------------------</OPTION>
<CFLOOP QUERY="allcategories"
    <OPTION VALUE="#categoryid#" <CFIF categoryid is detail.subcatofid>selected</CFIF>>
	#repeatstring("»", catdepth)# #category# (#itemscount#)<OPTION>
</CFLOOP>
</SELECT>
</TD>
</TR>

hope this helps

Q.:

How do you draw the list tree from the values returned! Please explain! And how to manage the updates/inserts?

A.:

We update catorder ans catdepth fields in category table to be able to pull entire tree within single "select" query to avoid numerous recursive calls which needs to be used otherwise and which are expensive in terms of server load and website performance. So once we updated catorder and catdepth fields we can draw list of nested categories with this simple code:

<CFQUERY name="getcategories" datasource="#REQUEST.ds#">
   select category,catdepth from category order by catorder
</CFQUERY>

<table>
<CFOUTPUT query="getcategories">
   <tr><td><li style="margin-left:#evaluate('catdepth*20')#px;">#category#</li></td></tr>
</CFQUERY>
</table>

Result output would look like this:

PHP Open Source

Product Types Examples

Coldfusion Open Source

Web Applications

SEO Tools

Business-to-Business (B2B)

Customer Service

E-Commerce

Regarding inserts/updates, they are the points when this custom tag is supposed to run.

Q.:

Will not that hinder the performance of the website?

A.:

It is bad for performance to call it often. It should run only when necessary (new category added or parent category changed). The last two events normally happen rarely and thus it can not hurt performance at all. On public side where you need to draw tree of nested categories, you don't need to run GetAnyBaby and only run query kind of

"SELECT categoryid,category,catdepth FROM categories ORDER BY catorder"

and you can draw tree from there

*Ask a Question about Custom tag to draw parent-child tree for an hierarchical table.:


E-Mail:		enter your email address if you would like to be notified when your question is answered
*Anti-spam code:
Please enter anti-spam code to prevent automated scripts' spam submissions. Thank You

Ready

Reviews Rate: Not Rated

Visitors Rate: (1 vote)

Rate this item: