Monday 29 July 2013

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 .= '\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='');

Monday 22 July 2013

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();

Tuesday 16 July 2013

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