How to Convert Database Results into Hierarchical Array Objects?

Converting Database Results into Hierarchical Array Objects

Implementing a Closure Table-Based Hierarchy

To efficiently represent hierarchical data, consider using a closure table. In this context, a closure table maintains relationships between ancestors and descendants of a tree structure, making it efficient to retrieve and represent the hierarchy.

Querying Hierarchical Data

To query the tree structure, we can execute a SQL query that retrieves the descendants of a given node along with their levels in the hierarchy. Here's an example query that retrieves descendants up to a specified level:

SELECT d.*, p.a AS '_parent'
FROM longnames AS a
JOIN closure AS c ON (c.a = a.tsn)
JOIN longnames AS d ON (c.d = d.tsn)
LEFT OUTER JOIN closure AS p ON (p.d = d.tsn AND p.l = 1)
WHERE a.tsn = ? AND c.l <h3 id="Processing-Query-Results">Processing Query Results</h3><p>Once the query results are retrieved, we can process them to create a hierarchical array structure. This involves sorting the rows by hierarchy and grouping them accordingly, creating an array with nodes as objects with both data and child arrays.</p><h3 id="Custom-Row-Objects">Custom Row Objects</h3><p>To enhance the array structure, we can create custom Row objects that contain an associative array of row data along with a Rowset object for their children. The children Rowset for leaf nodes is empty.</p><h3 id="toArrayDeep-Method">toArrayDeep() Method</h3><p>Custom classes can also include methods like toArrayDeep() that recursively convert their data content into a plain array, enabling easy data export.</p><h3 id="Example-Usage">Example Usage</h3><p>To use this system, one could first obtain an instance of the taxonomy table data gateway and then fetch the desired tree structure:</p><pre class="brush:php;toolbar:false">// Get an instance of the taxonomy table data gateway
$tax = new Taxonomy();

// Query tree starting at Rodentia (id 180130), to a depth of 2
$tree = $tax->fetchTree(180130, 2);

// Dump out the array
var_export($tree->toArrayDeep());

Additional Notes

To determine the depth of each path, you can insert new nodes into the Closure table, calculating the appropriate level based on where they are added in the hierarchy. The LAST_INSERT_ID() function can be useful for obtaining the ID of newly inserted nodes.

The above is the detailed content of How to Convert Database Results into Hierarchical Array Objects?. For more information, please follow other related articles on the PHP Chinese website!