如何将给定的多重连接查询转换为 ZendFrame 工作查询

Question

SELECT
 `blg`.`id` ,
 `blg`.`heading` , 
 `blg`.`description` ,
 `cat`.`name` AS `categoryname` ,
 `mem`.`firstname` AS `user` ,
  ( SELECT COUNT( * ) FROM blog_comments WHERE blog_comments.status = '1' AND blog_comments.blogid = blg.id) AS `commentcount`
    FROM `blogs` AS `blg`
  INNER JOIN `blog_category` AS `cat` ON blg.category = cat.id
  INNER JOIN `members_member` AS `mem` ON blg.user = mem.id
  LEFT JOIN `blog_comments` AS `cmt` ON blg.id = cmt.blogid
  WHERE (        blg.status = '1'        )
  GROUP BY blg.id ORDER BY `blg`.`id` ASC

Answer 1

像这样的东西怎么样：

        $db = Zend_Db_Table::getDefaultAdapter();
        $select = $db->select();

        $dbExpr = new Zend_Db_Expr("(SELECT COUNT( * ) FROM blog_comments WHERE blog_comments.status = '1' AND blog_comments.blogid = blg.id) AS `commentcount`");        

        $select->from(
                    array('blg' => 'blogs'), 
                    array('id', 'heading', 'description', 'name as categoryname', $dbExpr)
               )->joinInner(
                            array('cat' => 'blog_category'), 
                            'blg.category = cat.id',
                            array()
               )->joinInner(
                            array('mem' => 'members_member'), 
                            'blg.user = mem.id',
                            array()
               )->joinLeft(
                            array('cmt' => 'blog_comments'), 
                            'blg.id = cmt.blogid',
                            array()
               )->where('blg.status = ?', '1')
                ->group('blg.id')
                ->order('blg.id ASC');


        echo $select->assemble();
        exit;

输出：

SELECT `blg`.`id`, `blg`.`heading`, `blg`.`description`, `blg`.`name` AS `categoryname`, 
(SELECT COUNT( * ) FROM blog_comments WHERE blog_comments.status = '1' AND blog_comments.blogid = blg.id) AS `commentcount`
FROM `blogs` AS `blg` 
INNER JOIN `blog_category` AS `cat` ON blg.category = cat.id
INNER JOIN `members_member` AS `mem` ON blg.user = mem.id 
LEFT JOIN `blog_comments` AS `cmt` ON blg.id = cmt.blogid 
WHERE (blg.status = '1') 
GROUP BY `blg`.`id` 
ORDER BY `blg`.`id` ASC