php - Zend_Db 嵌套查询，其中一个查询需要另一个查询的 ID

Question

我是 Zend Framework 的新手，搜索了好几天都没有找到以下问题的示例:

我试图从下面的代码中获得的结果是一个列表，其中包含主要类别及其下方的匹配子类别，并循环遍历我的所有主要类别。

例子:

电器(主要类别)

• 微波炉
• 炉子

电子(主要类别)

• 计算机
• 广播

这是代码(我还是不知道该怎么做；我的想法是做嵌套的 foreach()，第二个 foreach() 得到第一次选择的主要类别 ID):

// 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

你能试试这个吗:

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

结果将是一个对象数组，如下所示:

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

希望对您有所帮助!

问候，艾哈迈德。