Get all categories (multilevel)

Question

I'm using codeigniter and have a table with 3 columns (id, name, parent_id). A category can have many subcategories and a subcategory can have many sub-sub categories.

I've been trying to get all categories and their subcategories using this code:

public function getCategory($id)
{
        $categories = array();
        while($id != 0)
        {
                $this->db->from('categories'); //$this->table is a field with the table of categoris
                $this->db->where('id', $id);
                $this->db->limit(1);
                $result = $this->db->get()->row(); //fetching the result
                $categories[] = $result;
                $id = $result->parent_id;
        }
        return $categories;
}

   public function getAllCategories()
    {
            $this->db->select('id');
            $this->db->from('categories'); //$this->table is a field with the table of categoris
            $this->db->where('parent_id', 0);
            $mainCategories = $this->db->get()->result(); //fetching the result
            $result = array();
            foreach($mainCategories as $id)
            {
                    $result[] = $this->getCategory($id->id);
            }
            return $result;
    }

But it returns me only 1 level categories.

My question is how to accomplish my task: get all categories and subcategories for every level.

Answer 1

The simplest solution to your problem would be to add recursion.

public function getCategoryTreeForParentId($parent_id = 0) {
  $categories = array();
  $this->db->from('categories');
  $this->db->where('parent_id', $parent_id);
  $result = $this->db->get()->result();
  foreach ($result as $mainCategory) {
    $category = array();
    $category['id'] = $mainCategory->id;
    $category['name'] = $mainCategory->name;
    $category['parent_id'] = $mainCategory->parent_id;
    $category['sub_categories'] = $this->getCategoryTreeForParentId($category['id']);
    $categories[$mainCategory->id] = $category;
  }
  return $categories;
}

This approach can be greatly increased in speed by preloading all categories and operating over the array, thus skipping the query for each new parent_id.

Also i did not use the objects of codeigniter, as i don't know them. If they support magic setters / getters or can be convinced to take a array of child objects, they should be used instead the the array i build here.

What the algorithm does is: load all categories with a given parent_id, loop through all those categories, assume the iteration-category-id as parent_id and load everything for it. This effectively loads all categories, as long as they reference existing categories.

There is a slight danger involved: When you have construct of categories A and B where A has B as parent and B has A as parent, you will run into an endless loop, as they load each other again and again. This forces you to have a clean tree structure in your data.

Update

As this still gets upvotes: There is another advice concerning this implementation. If your category tree is bigger or has multiple levels, you might run into performance issues, as this implementation loads the categories again and again with new query parameters. This can quite easily result in dozens, hundreds or thousands of queries, depending on your category tree.

A quite more efficient way is to load all categories (with one query) from your category table and sort them with recursion within your application. This is one of the rare cases, where early evaluation does improve performance.

If the tree is required more than once within the same request, one could even add caching through static variables (with all the usual dangers of caching).

Answer 2

This is a codeigniter library i found it in an answer here in stackoverflow but i don't remember its owner. I made some modification to it to return the nested categories as a list. its works very well with me.

<?php
if (!defined('BASEPATH'))
    exit('No direct script access allowed');
class Tree {

    var $table = '';
    var $CI = '';
    var $ul_class = '';
    var $iteration_number = 0;
    function Tree($config = array()) {
        $this -> table = $config['table'];
        $this -> CI = &get_instance();
        $this -> CI -> load -> database();
        $this -> ul_class = $config['ul_class'];
    }

    function getTree($parent_id = 1) {
        $this -> iteration_number++;
        $this -> CI -> db -> where('parent_id', $parent_id);
        $first_level = $this -> CI -> db -> get($this -> table) -> result();
        if($this->iteration_number == 1)
            $tree = '<ul id="red" class="' . $this -> ul_class . '">';
        else
            $tree = '<ul>';
        foreach ($first_level as $fl) {
            $this -> CI -> db -> where('parent_id', $fl -> category_id);
            $count = $this -> CI -> db -> count_all_results($this -> table);
            if ($count != 0) {
                $tree .= '<li><span>' . $fl -> category_name . '</span>';
                $tree .= $this -> getTree($fl -> category_id);
            } else {
                $tree .= '<li><a href="'.base_url().'categories/sub/'.str_replace(' ', '-', $fl -> category_name).'" cat_id="'.$fl -> category_id.'" class="leaf">' . $fl -> category_name . '</a>';
            }
            $tree .= '</li>';
        }
        $tree .= '</ul>';
        return $tree;
    }
}
 ?>

You can use it from your controller as the following:

$config['ul_class']='treeview';
$config['table']='categories';
$this->load->library('tree',$config);
$tree = $this->tree->getTree();

and the structure of the DB table as the following:

CREATE TABLE IF NOT EXISTS `categories` (
  `category_id` int(4) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `category_description` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `parent_id` int(4) NOT NULL,
  PRIMARY KEY (`category_id`),
  KEY `categories_categories_fk_idx` (`parent_id`)
) ENGINE=InnoDB 

You can modify the returned result by the getTree function in the library to return what you need.

Answer 3

I'm going to offer up a different solution, although you'll need to do some slight restructuring:

Using a lineage-style hierarchy, you can avoid all of the recursion issues & have much faster data pulls. You mark each category with a full list of ancestors, such as: 0001-0005-0015 ... etc and then can run simple queries with a LIKE to get your subqueries.

I have a Codeigniter library on git & blog post explaining how it all works: http://codebyjeff.com/blog/2012/10/nested-data-with-mahana-hierarchy-library

Answer 4

Assuming your top-level categories have parent_id = 0. You can get your data from the DB with the following code, and create a 2-dimension array

$this->db->from('categories');
$query = $this->db->get();
$rows = $query->result()) {
// ok, we have an array ($rows). We need it
// transformed into a tree
$categories = array();
foreach ($rows as $category) {
   // add the category into parent's array
   $categories[$category->parent_id][] = $category;
}    
return $categories;

You can then traverse the $categories array, given that the $categories[0] is an array holding the top level categories, and $categories[$i] is holding the subcategories of the category (or subcategory) with id = $i.