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
这样的东西怎么样：
$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