Flat database rows are easy to store, but they are awkward to render when the UI wants a nested menu, category tree, org chart, or threaded comment list.
Most hierarchical data is stored with a simple relationship:
Each row has its own `id`
Each row may have a `parentID`
Rows with no parent are top-level items
Rows with a parent belong under another row
That storage model works well in SQL. It keeps the table simple and avoids nesting data inside the database. But once those rows reach CFML, you often need a real tree, where each node contains a `children` array.
The clean way to build that tree is a two-pass lookup pattern. You create every node first, store those nodes in a struct keyed by ID, then link each node to its parent. This gives you predictable linear performance and avoids repeated scans through the full dataset.
What you are building
The goal is to turn flat records like this:
id | parentID | name |
1 | Products | |
2 | 1 | Cameras |
3 | 1 | Lenses |
4 | 2 | Mirrorless Cameras |
5 | 2 | DSLR Cameras |
6 | Support | |
7 | 6 | Documentation |
Into a nested structure like this:
```json
[
{
"id": 1,
"name": "Products",
"children": [
{
"id": 2,
"name": "Cameras",
"children": [
{
"id": 4,
"name": "Mirrorless Cameras",
"children": []
},
{
"id": 5,
"name": "DSLR Cameras",
"children": []
}
]
},
{
"id": 3,
"name": "Lenses",
"children": []
}
]
},
{
"id": 6,
"name": "Support",
"children": [
{
"id": 7,
"name": "Documentation",
"children": []
}
]
}
]
```
That nested array is much easier to use when rendering navigation, building API responses, or passing data to a front-end component.
This is the core pattern for a CFML tree structure for hierarchical data from a flat table.
Start with the right flat data model
Before writing the CFML, make sure the source data has the basic fields the tree builder needs.
At minimum, each record needs:
A unique ID, usually `id`
A parent reference, usually `parentID`
Any display or payload fields, such as `name`, `slug`, `sortOrder`, or `url`
For example:
```cfml
records = [
{ id = 1, parentID = "", name = "Products" },
{ id = 2, parentID = 1, name = "Cameras" },
{ id = 3, parentID = 1, name = "Lenses" },
{ id = 4, parentID = 2, name = "Mirrorless Cameras" },
{ id = 5, parentID = 2, name = "DSLR Cameras" },
{ id = 6, parentID = "", name = "Support" },
{ id = 7, parentID = 6, name = "Documentation" }
];
```
Rows with an empty `parentID` become roots. If your database uses `NULL`, `0`, or another value to represent the top level, you can adjust the root check later.
The important rule is simple: every non-root record should point to another record by ID.
Avoid the recursive search trap
A common first version looks something like this:
```cfml
function findChildren(required array records, required any parentID) {
var children = [];
for (var record in arguments.records) {
if (record.parentID == arguments.parentID) {
record.children = findChildren(arguments.records, record.id);
arrayAppend(children, record);
}
}
return children;
}
```
This works for small lists. The problem is that each recursive call loops over the full array again.
If you have 20 rows, that is probably fine. If you have hundreds or thousands of rows, the repeated scanning becomes expensive. A large product catalog, comment thread, or organization chart can expose the weakness fast.
The issue is not recursion by itself. The issue is the full-list search inside each recursive step.
A better pattern avoids searching for children over and over. Instead, it builds a direct lookup table once.
Build a lookup struct for every node
The first pass creates one node for each row and stores it in a struct.
The key is the record ID. The value is the node that will later appear in the tree.
```cfml
nodesById = {};
for (var record in records) {
var id = record.id;
nodesById[id] = duplicate(record);
nodesById[id].children = [];
}
```
After this pass, `nodesById` acts like an index:
```cfml
nodesById[1] // Products node
nodesById[2] // Cameras node
nodesById[6] // Support node
```
This gives you constant-time access to any parent node by ID. You no longer need to scan the full list to find a record.
The `duplicate()` call keeps the tree nodes separate from the original row data. That is helpful if you do not want to mutate the original records. If mutation is fine in your use case, you can skip `duplicate()`, but using it makes the function safer and easier to reuse.
Each node gets an empty `children` array right away. That means every node has the same shape, even leaf nodes.
That consistency matters when rendering. Your view code can safely check `node.children` without wondering whether the key exists.
Link each node to its parent
The second pass attaches each node to its parent.
For each record:
Get its current node from `nodesById`
Check whether it has a parent
If it has no parent, add it to the top-level `tree` array
If it has a parent, find the parent in `nodesById`
Append the current node to the parent’s `children` array
Here is the basic version:
```cfml
tree = [];
for (var record in records) {
var id = record.id;
var parentID = record.parentID;
var node = nodesById[id];
if (!len(trim(parentID & ""))) {
arrayAppend(tree, node);
} else if (structKeyExists(nodesById, parentID)) {
arrayAppend(nodesById[parentID].children, node);
}
}
```
This is the key move. You are not searching for children. You are directly finding each parent and adding the child to it.
Build all nodes first, then link them. That one choice prevents ordering bugs and keeps the algorithm fast.
The order of the source rows no longer matters. A child can appear before its parent in the original query because the first pass creates every node before the second pass links anything.
Put the pattern in a reusable CFML function
A reusable function keeps the tree-building logic out of your controllers, views, and service methods.
This version accepts an array of structs and lets you choose the ID field and parent field names.
```cfml
public array function buildTree(
required array rows,
string idKey = "id",
string parentKey = "parentID"
) {
var nodesById = {};
var tree = [];
// First pass: create every node.
for (var row in arguments.rows) {
var id = row[arguments.idKey];
nodesById[id] = duplicate(row);
nodesById[id].children = [];
}
// Second pass: attach each node to its parent.
for (var row in arguments.rows) {
var id = row[arguments.idKey];
var parentID = row[arguments.parentKey];
var node = nodesById[id];
if (!len(trim(parentID & ""))) {
arrayAppend(tree, node);
} else if (structKeyExists(nodesById, parentID)) {
arrayAppend(nodesById[parentID].children, node);
} else {
// Orphan handling.
// Treat records with missing parents as top-level nodes.
arrayAppend(tree, node);
}
}
return tree;
}
```
Use it like this:
```cfml
tree = buildTree(records);
```
Or with different column names:
```cfml
tree = buildTree(
rows = records,
idKey = "categoryID",
parentKey = "parentCategoryID"
);
```
The function treats orphaned records as top-level nodes. An orphan is a record with a `parentID` that does not exist in the input data.
That fallback is often the safest behavior for display code. It prevents data problems from making the whole tree disappear. In stricter systems, you may prefer to throw an error or collect orphan records separately.
For example:
```cfml
} else {
throw(
type = "TreeBuilder.MissingParent",
);
}
```
Choose the behavior that fits the job. For admin navigation, showing the orphan may be fine. For accounting categories or permission trees, failing loudly may be better.
Use query data without complicating the tree builder
Many CFML apps start with a query rather than an array of structs.
You can either adapt the function to loop over a query, or convert the query into an array first. Keeping the tree builder focused on arrays often makes it easier to test.
Example query:
```cfml
qCategories = queryExecute("
SELECT
categoryID,
parentCategoryID,
categoryName,
slug
FROM categories
ORDER BY sortOrder, categoryName
");
```
Convert that query to an array:
```cfml
records = [];
for (var row in qCategories) {
arrayAppend(records, {
categoryID = row.categoryID,
parentCategoryID = row.parentCategoryID,
categoryName = row.categoryName,
slug = row.slug
});
}
categoryTree = buildTree(
rows = records,
idKey = "categoryID",
parentKey = "parentCategoryID"
);
```
Now your tree builder does not care where the data came from. It can work with query results, API data, cached JSON, or test fixtures.
That separation also makes unit testing much simpler. You can pass a small array into `buildTree()` and assert that the output matches the expected nested shape.
Keep sibling order predictable
The lookup pattern preserves the order in which children are appended.
That means sibling order depends on the order of your input data. If your menu, category tree, or comment thread needs a specific order, sort before building the tree.
For database-backed records, the easiest place is usually SQL:
```sql
ORDER BY sortOrder, name
```
If you sort after building the tree, you need to sort each `children` array at every level. That is possible, but it adds extra work and more code.
For most apps, sort the flat data first, then build the tree.
Handle root values clearly
Different systems represent root nodes in different ways.
Common root values include:
Root style | Example |
Empty string | `parentID = ""` |
Null value | `parentID IS NULL` |
Zero | `parentID = 0` |
Fixed root record | `parentID = 1` |
The sample function treats empty strings and null-like blank values as roots because this line converts the value to a string before trimming it:
```cfml
if (!len(trim(parentID & ""))) {
```
If your app uses `0` as the root marker, adjust the condition:
```cfml
if (!len(trim(parentID & "")) || parentID == 0) {
arrayAppend(tree, node);
}
```
If your app has a conceptual root record, such as `parentID = 1`, you may want to collect records under that root as top-level nodes instead of including the root itself.
```cfml
rootParentID = 1;
if (parentID == rootParentID) {
arrayAppend(tree, node);
}
```
The best choice depends on what you want to return. Some APIs include a synthetic root node. Others return only the visible top-level children.
Know why the two-pass approach scales better
The naive recursive-search version tends toward O(n²) behavior because each node may trigger a scan through the full list.
The lookup version is O(n):
One pass to create nodes
One pass to link nodes
Direct parent lookups through a struct
That difference becomes real once the list grows beyond a small menu. A category tree with thousands of products, a documentation site with many sections, or a threaded discussion with many replies will benefit from the lookup approach.
The code is also easier to reason about. First, everything exists. Then, everything gets linked.
Watch out for interleaved linking
One tempting variation is to create each node and attach it to its parent in the same loop.
That can fail when records arrive in an unexpected order.
For example:
```cfml
records = [
{ id = 4, parentID = 2, name = "Mirrorless Cameras" },
{ id = 2, parentID = 1, name = "Cameras" },
{ id = 1, parentID = "", name = "Products" }
];
```
If you try to attach node `4` to node `2` during the same pass where you create node `4`, node `2` does not exist yet.
You could add extra checks and delayed processing, but that makes the code more complex than it needs to be.
The two-pass pattern avoids this entire class of bugs. It does not matter whether the parent appears before or after the child. By the time linking starts, every node is already available in `nodesById`.
Render the finished tree
Once you have the nested array, rendering becomes straightforward.
Here is a simple recursive function that renders nested HTML lists:
```cfml
public string function renderTree(required array nodes) {
var html = "<ul>";
for (var node in arguments.nodes) {
html &= "<li>";
html &= encodeForHtml(node.name);
if (arrayLen(node.children)) {
html &= renderTree(node.children);
}
html &= "</li>";
}
html &= "</ul>";
return html;
}
```
Use it like this:
```cfml
writeOutput(renderTree(tree));
```
For JSON APIs, you can return the tree directly:
```cfml
cfcontent(type = "application/json");
writeOutput(serializeJSON(tree));
```
The same tree can serve many outputs:
Nested navigation menus
Category pickers
Comment threads
Sidebar documentation trees
Organization charts
Permission groups
The tree builder only handles data shape. Rendering stays separate.
What success looks like
You have the right solution when your flat records become a nested array where:
Every node has a `children` array
Top-level records appear in the root array
Child records appear under the correct parent
Source row order does not break parent-child linking
Large datasets do not require repeated full-list scans
The practical pattern is simple: create every node first, then link every node second.
That small shift turns flat table data into a reliable CFML tree structure without expensive recursive searching. Keep the builder generic, sort your input before building, and decide how your app should handle orphan records. Once those choices are clear, the same function can support menus, catalogs, org charts, comments, and any other hierarchy your CFML app needs.