Zend_Db nested queries where one query needs the ID from the other

Question

I am fairly new to Zend Framework and have searched for days without finding an example for the following issue:

The result I am trying to achieve from the code below is a list that would have the main Category with the matching sub categories beneath it and loop through for all my main categories.

Example:

Appliances (Main Category)

• Microwave
• Stove

Electronics (Main Category)

• Computer
• Radio

Here is the code (again I have no idea how to do this; my thought process was to do nested foreach(), with the second foreach() getting the main category id from the first select):

// Get  Categories with Sub Categories

$catid = 0;

$selectmain = $this->dbhInstance->select()
                                ->from(array('a' => 'code_sub_category'), 
                                       array('b.id as mainid', 'b.site_category'))
                                ->join(array('b' => 'site_categories'), 
                                       'b.id = a.site_category_id')
                                ->group("b.id")
                                ->order("b.site_category");

$selectsub = $this->dbhInstance->select()
                               ->from(array('a' => 'code_sub_category'), 
                                      array('a.id as subid', 'a.sub_category', 'a.site_category_id'))
                               ->join(array('b' => 'site_categories'), 
                                      'b.id = a.site_category_id')
                               ->where("a.site_category_id = '" . $catid . "'")
                               ->order("a.sub_category");

$fetch = $this->dbhInstance->fetchAll($selectmain);
$fetch2 = $this->dbhInstance->fetchAll($selectsub);
//var_dump($fetch2);

$items = array();
$items2 = array();

foreach ($fetch as $key => $value) {
    $catid = $value['id'];
    $items = array_merge($items, array($key => $value));

    foreach ($fetch2 as $key => $value) {
        $items = array_merge($items, array($key => $value));
    }

    $this->view->getsubcategories = $items;
}

$this->view->getmaincategories = $items;
//End FULL Categories for My Categories

Answer 1

Can you try this :

$selectCat = $this->dbhInstance->select()
                            ->from(array('c' => 'site_categories'), array('c.id as id', 'c.site_category as text, 0 as parent'))
                            ->order('c.site_category');

$selectSubCat = $this->dbhInstance->select()
                            ->from(array('sc' => 'code_sub_category'), array('sc.id as id', 'sc.sub_category as text', 'sc.site_category_id as parent')
                            ->order('sc.sub_category');

$select =   $this->dbhInstance->select()->union(array($selectCat, $selectSubCat));

$statement = $this->dbhInstance->query($select);

$items = array();
$childs = array();

while( $row = $statement->fetch() ) {
     $items[] = (object)array(
    'id' => $row['id'],
    'text' => $row['text'],
    'parent' => $row['parent'],
    'item' => array()
);
}

foreach($items as $item)
$childs[$item->parent][] = $item;

foreach($items as $item) if (isset($childs[$item->id]))
$item->item = $childs[$item->id];

if( !isset($childs[0]) ) $this->view->getmaincategories = $getmaincategories = array();
else 
$this->view->getmaincategories = $getmaincategories = $childs[0];

// Display the result 
Zend_Debug::dump($getmaincategories);

The result will be an array of objects, something like this :

array(
'Appliances' => array(
    'Microwave',
    'Stove'
),
'Electronics' => array(
    'Computer',
    'Radio'
)
)

Hope this help!

Regards, Ahmed.