SEM Labs

Handcrafted Pixels, Code & Title Tags

Converting Nested Set Model Data in to Multi-dimensional Arrays in PHP

If you are using the Nested Set Model to store hierarchical data in SQL, this PHP function will help you convert that data from a flat array into a multi-dimensional array.

The function requires that you query some data with a value representing it's hierarchical depth. You can do this using a query like:

	
		SELECT node.name, (COUNT(parent.name) - 1) AS depth
		FROM nested_category AS node,
		nested_category AS parent
		WHERE node.lft BETWEEN parent.lft AND parent.rgt
		GROUP BY node.name
		ORDER BY node.lft;
	

In this query, a value will be returned to signify the row's depth using the key 'depth'. Once you have your data in associative array with depth key, pass the array to the function as the first argument and pass the name of the depth key as the second argument. The second argument defaults to 'depth'. The function will return something like this:

If you're not familiar with the Nested Set Model, you can read more about it here. I've also heard it referred to at the infinite category table thingy.

It's a simple technique that is good for storing such things as categories or hierarchical site structure in a table. Rather than using multiple queries or multiple joins on the same table using aliases, the Nested Set Model allows you to store hierarchical data in a manner that requires less bloat when it comes to querying.

This function is available under the MIT License.

Comments

Janice Kong Replied at 12:13 AM on 10 Sep 2009

Thank you for your code, i have been searching for this.

Matthew Fedak Replied at 9:34 AM on 21 Oct 2009

thankyou so much for this, been trying to figure it out myself for past 2 hours. doh! I'm now trying to put it in a nice un ordered list for a site menu via smarty.

Kiers Replied at 4:18 PM on 7 Jul 2010

Cheers for sharing this - it was just the push I needed!

I needed it to work slightly differently though - rather than numbered indexes, I wanted a 'children' key in the array, containing zero-based indexed sub arrays....

here's a revised version that does that:

function nestify( $arrs, $depth_key = 'depth' ) {

$nested = array();

foreach( $arrs as $key => $arr ) {

if( $arr[$depth_key] == 0 ) {

$nested[] = $arr->toArray();

}

else {

$parent =& $nested;

for( $i = 1; $i toArray();

}

}

return $nested;

}

Thanks again

K

James Moss Replied at 8:48 AM on 28 Jul 2010

Thanks for posting this, it worked a treat. Ive been pulling my hair out for the last hour trying to get my head round this.

Muhammad Ahsun ALi Replied at 10:03 AM on 6 Sep 2011

The script is good and works well for small depths. It does not return every column for branches. Also it duplicates every node array.

Reid Replied at 8:08 PM on 16 Nov 2011

To the previous commentor's post - the problem with the query presented here (taken from elsewhere) is that it requires the nested_category.name column contain unique values. If there's overlap (e.g. two rows w/ the same name), the query yields bad results.

Try referencing a column with unique values in the COUNT and GROUP BY clauses - problem solved. Say your unique field is 'id':

SELECT node.name, (COUNT(parent.id) - 1) AS depth

FROM nested_category AS node,

nested_category AS parent

WHERE node.lft BETWEEN parent.lft AND parent.rgt

GROUP BY node.id

ORDER BY node.lft;

Post Comment

Thin comments left for links will be deleted.

Entry Info

Categories