[snip]
On 3/22/2012 11:17 AM, Robert Cummings wrote:
On 12-03-22 11:58 AM, Jay Blanchard wrote:
[snip]
Fix this code... I've come across codebases that did this specific
type of nested querying and it resulted in 10000 queries to the
database on every page. Instead, create a layered approach:
1. Select your root elements.
2. Loop over in PHP and create an array of child IDs.
3. Select the children from the database.
4. Go to step 2.
This way you will only every perform "depth" number of queries.
[/snip]
I see what you're saying but I don't know that this reduces the number
of queries - it just handles them in a different order. How do I get to
the output that I need?
It definitely reduces the queries... your current method gets all the
first level nodes in one query, then performs a query for every single
parent node. Mine only performs a query for each level in the tree. If
you have 5 nodes at the first level you will perform 6 queries. Mine
will perform 2.
[/snip]
How so? A query must be performed for each parent node to get its children.
parent
|
child child child child
|
grandchild grandchild
[snip]
To generate the nesting structure at each level you track the level
members. Something like the following (untested pseudoish):
<?php
$parents = query_for_first_level();
$root = &$parents;
while( $parents )
{
$parentIds = array();
foreach( $parents as $parent )
{
$parentIds[$parent['id']] = $parent['id'];
}
$children = query_for_children( $parentIds );
foreach( $children as &$child )
{
$parents[$child['parentId']]['children'][] = &$child;
}
$parents = &$children;
}
$jsonData = JSON_encode( $root );
?>
[/snip]
I'll try to apply this and see what I run into.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
