Create the N-level Categories tree with single SQL query using CI

To create the N-level Categories tree with single SQL query using CI

class Category_model extends Model {

 private $table = 'categories';
 private $selectTreeOptions_data = null;
 private $selectTreeOptions_index = null;
 
 public function get_categories() {
  $this->db->select('c.id, c.parent_id, c.name');
  $this->db->from($this->table .' AS c');
  $this->db->where('c.status', '1');
  
  $query = $this->db->get();
  //$str = $this->db->last_query();
  
  return $query->result_array();
 }
 
 /*
  * Recursive top-down tree traversal example:
  * Indent and print child nodes
  */
 function getSelectTreeOptions($parent_id, $level, $selected_id='')
 {
  $html = '';
     $data = $this->selectTreeOptions_data;
     $index = $this->selectTreeOptions_index;
     $parent_id = $parent_id === NULL ? 0 : $parent_id;
    
     if (isset($index[$parent_id])) {
         foreach ($index[$parent_id] as $id) {
          $selected = isset($selected_id) && ($selected_id==$data[$id]["id"]) ? 'selected="selected"' : '';
             $html .= ' '.str_repeat("-", $level) .' '. $data[$id]["name"] . " (".$data[$id]["id"].")\n";
             $html .= $this->getSelectTreeOptions($id, $level + 1, $selected_id);
         }
     }
          
     return $html;
 }

 // Get the category tree with select options.
 function get_categories_tree_options($selected_id='')
 {
   $categories = $this->get_categories();
  $this->data = '';
  $this->index = '';
  
  foreach ($categories as $row) :
      $id = $row["id"];
      $parent_id = $row["parent_id"] === NULL ? "NULL" : $row["parent_id"];
      $this->selectTreeOptions_data[$id] = $row;
      $this->selectTreeOptions_index[$parent_id][] = $id;
  endforeach;
  
  $items = $this->getSelectTreeOptions(NULL, 0, $selected_id);
  
  return $items;  
 }
 
}

Category_model::get_categories_tree_options($selected_id='');

Joomla + GROUP_CONCAT (ORDER BY, SEPARATOR)

To fetch the Joomla Categories in sorted order, GROUP_CONCAT and order by with seperator.

/* Fetching all the Childs skills into a list. */
$this->_db->setQuery($this->_db->getQuery(true)
 ->from('#__categories')
 ->select('GROUP_CONCAT(title ORDER BY lft ASC SEPARATOR ", ") as skills')
 ->where('parent_id="'.(int)$parent_id.'" AND published="1"')
 ->order('lft ASC'));
$child = $this->_db->loadObject();

Reminiscence always leftovers

Why someone can do such a silly mistake 
by making away with oneself...

Why the other side of the surrounding couldn't be seen, 
though family and friend always there for good and either. 

Difficulties ever opted to be allotted for dissolution 
and none would be misfortunates. 

Certainly, it can't be unfasten, 
nevertheless reminiscence always leftovers. 

Alas! overwhelming compassion to those ones... :-(

~ in memory of someone