How to build menu with single query?

[eluser]umefarooq[/eluser]
How can we make n level tree or menu with just single query, no need to call SQL query in loop to get its children, just get everything with one query and make tree or menu out of it, anybody know any good library,code or helpers please share, will help all CI community.

[eluser]Zorancho[/eluser]
You need to use recursive function to create tree or menu.

[eluser]Johan André[/eluser]
You can build trees in databases in various ways:

1. Adjacency List - this is using parent_id pointing to the parent (doh!). Not possible to fetch the tree using one query.

2. Modified Preorder Tree Traversal - this tree is "harder" to update but lightning fast at fetching. This is the one you should use when getting a menu with multiple levels in just one query.

Check out the forum. There is something called MPTTree that does just this...

[eluser]umefarooq[/eluser]
i have found some thing hope will work for everybody im using parent id but not keeping depth so MPTree will not good for me everybody have look on the script may be some has good ideas than this.

N level menu

[eluser]Zorancho[/eluser]
Yes it's a good practice to use recursive function and build an array with all the relationships.
Check out the CI code is using lots of recursive functions such as:

Code:

function escape_str($str)
{
   if(is_array($str))
   {
      foreach($str as $key => $val)
      {
         $str[$key] = escape_str($val);
      }
      return $str;
   }
   else
   {
      return mysql_real_escape_string($str);
   }
}

[eluser]Jelmer[/eluser]
I prefer using multiple joins of joining the table on itself because it allows me to fetch a subtree without fetching the entire tree. The disadvantage is that you have to limit the amount of levels you get.

Here's an example of actual code I'm using to build such a query with RapidDataMapper, should be easily understandable even if you don't know RDM as it looks much like AR.

Code:

// Array of the columns to be selected
$columns = array('id', 'title', 'page_id');
// Max depth has been previously set to a default value
$depth = $this->max_depth;

// Set the columnname for each level to be queried by prefixing them with s#_ (ie. s1, s2, s3)
$columns_array = array();
for ($x = 1; $x <= $depth; $x++)
    foreach ($columns as $c)
        $columns_array[$x]['s'.$x.'_'.$c] = $c;

// Select the page table as s1 with the first level of the columns_array for selection
$q = Db::getConnection()->select()->from(array('s1' => 'page'), $columns_array[1]);

// Foreach level in $depth join the page table on itself and use the columns_array
// to select the columns with their prefixed aliasses
for ($x = 2; $x <= $depth; $x++)
    $q->join(array('s'.$x => 'page'),
        's'.$x.'.page_id = s'.($x-1).'.id',
        $columns_array[$x],
        'left');

// Require the base level, in this case it's the root 0
$q->where('s1.page_id', 0);

You'll still have to parse the outcome though.

I'm currently using this to fetch the breadcumbs trail of a page by adding the following:

Code:

// First try if the requested ID ($page_id) is in the first row
// (the first empty where() function puts the next where statements within brackets until
// end() is called)
$q->where()->where('s1.id', $page_id);
// Add alternative (or) where's for each level to be gotten
for ($x = 2; $x <= $depth; $x++)
    $q->where('or s'.$x.'.id', $page_id);
$q->end();

Though the result of that has to be cleaned up because if the page has subpages within the given depth the first subpage and possible sub-subpage will be fetched as well.
So after it's been fetched I backtrace the result and unset the fields that shouldn't be there:

Code:

for ($x = $depth; $x >= 1; $x--)
{
    // when the ID is found no further entries have to be deleted from the result
    if ($parents->{'s'.$x.'_id'} == $page_id)
        break;
    // Unset the values if it's not the page we were looking for
    foreach ($columns as $c)
        $parents->{'s'.$x.'_'.$c} = NULL;
}

[eluser]JoostV[/eluser]
Try MPTtree. A great lib by m4rw3r.

[eluser]Johan André[/eluser]
[quote author="Zorancho" date="1266168314"]Yes it's a good practice to use recursive function and build an array with all the relationships.
Check out the CI code is using lots of recursive functions such as:

Code:

function escape_str($str)
{
   if(is_array($str))
   {
      foreach($str as $key => $val)
      {
         $str[$key] = escape_str($val);
      }
      return $str;
   }
   else
   {
      return mysql_real_escape_string($str);
   }
}

[/quote]

Well, the problem is not using recursive calls. It's (usually) a performance-problem when doing one query for each level. Ofcourse, using just a couple of levels will not affect performance alot if you index your tables correctly...