CFX_MAKE

CFX_MAKE_TREE

CFX_MAKE_TREE allows you to structure nested data without having to use multiple recursive inner joins in your database. It returns data organized in group hierarchies allowing you to place the data in a CFFORM tree control using the CFTREE tag. Introduction One possible model for storing tree structures with unlimited nesting in a databases is to use a table with two fields:

ItemID (unique identification key)

ParentItemID (reference to the parent key in the same table)

Each tree node is then stored in a separate record, where the ItemID field contains its unique identification value. The parent-child relationship between nodes (records) is determined by the value of the field ParentItemID, which stores the identification value of the nodes’ parent for each record.

An example of such table would be:

ItemID	ParentItemID	Description
1	0	Animals
2	1	Reptiles
3	1	Birds
4	1	Mammals
5	2	Rattlesnake
6	3	Eagle
7	3	Chicken
8	3	Turkey
9	4	Elephant
10	4	Giraffe

Often we need to have records organized in a fashion, which will allow us to represent the tree visually. For the example above it would look like:

ItemID	ParentItemID	Description
1	0	Animals
2	1	Reptiles
3	1	Birds
4	1	Mammals
5	2	Rattlesnake
6	3	Eagle
7	3	Chicken
8	3	Turkey
9	4	Elephant
10	4	Giraffe

From the this recordset it is then straight-forward to generate an HTML code
<UL>
<LI>Animals
    <UL>
    <LI>Reptiles
        <UL>
        <LI>Rattlesnake</LI>
        </UL>
    </LI>
    <LI>Birds
        <UL>
        <LI>Eagle</LI>
        <LI>Chicken</LI>
        <LI>Turkey</LI>
        </UL>
    </LI>
    <LI>Mammals
        <UL>
        <LI>Elephant</LI>
        <LI>Giraffe</LI>
        </UL>
    </LI>
</LI>
</UL> The problem is how to retrieve the recordset with records organized in a tree structure. One solution is to use recursive inner joins on the table. This method is very time- and resource-intensive, especially for large tables. It would be much simpler to retrieve the records in an unorganized fashion and then structure the records using the CFX_MAKE_TREE tag.
Syntax <CFX_MAKE_TREE QUERY="query_name"> Usage CFX_MAKE_TREE takes the recordset specified in the QUERY attribute, containing at least two fields, one with name ItemID and one with name ParentItemID. The extension organizes the records into a tree structure as shown above. When the recordset is used anywhere below the CFX_MAKE_TREE tag, the records are organized in this fashion.

Note: If you are using different name for fields ItemID and ParentItemID, you can alias your field names with these names, when retrieving the recordset. For examplethe following SQL statement aliases the retrieved fields:

SELECT MyItemID AS ItemID, MyParentItemID AS ParentItemID, ...

To register the CFX_MAKE_TREE custom CFX for Cold Fusion, open the Cold Fusion Administrator, Tags page.

Example <CFQUERY NAME="AnimalKingdom" DATASOURCE="MyDS">
SELECT ItemID, ParentID, Description FROM Animals
</CFQUERY>

<CFX_MAKE_TREE QUERY="AnimalKingdom">