php - Complex query in ZEND -

i want execute following query in zend 1.12.

select b . * , count( * ) ct `blogpost` b join `blog_likes` l b.`blog_id` = l.`blog_id` , l.liked_post = 'y' group b.`blog_id` order ct desc limit 0 , 10 

i tried following code, not returning correct output want :

$this->getdbtable()->select() ->setintegritycheck(false) ->from(array('b' => 'blogpost'), array( "b.*", "count(*) ct")) ->join(array('l' => 'blog_likes'), 'b.blog_id = l.blog_id')                  ->where("l.liked_post =?", 'y') ->group ("b.blog_id") ->order ("ct desc") ->limit($limit); 

can 1 please me?

first of all, need pass , every join write, columns want select table, thus, if don't want select column l table, join should like:

->join(array('l' => 'blog_likes'), 'b.blog_id = l.blog_id', array()) 

if leave last parameter empty, it'll select columns (l.*).

second, array of columns pass or join, can set aliases using value of key, i.e.:

 ->from(array('b' => 'blogpost'), array( "b.*", "ct" => "count(*)")) 

this makes more readable.

and last, not least, counting on from, won't count rows inner table has, from's. so, if want count rows final table, guess trying sql sentence, have is:

$this->getdbtable()->select()     ->setintegritycheck(false)     ->from(array('b' => 'blogpost'), array( "b.*"))     ->join(array('l' => 'blog_likes'), 'b.blog_id = l.blog_id', array("ct" => "count(*)"))                      ->where("l.liked_post =?", 'y')     ->group ("b.blog_id")     ->order ("ct desc")     ->limit($limit); 

the count can little bit strange use, suggest use way instead:

$this->getdbtable()->select()     ->setintegritycheck(false)     ->from(array('b' => 'blogpost'), array())     ->join(array('l' => 'blog_likes'), 'b.blog_id = l.blog_id', array())                      ->where("l.liked_post =?", 'y')     ->group ("b.blog_id")     ->order ("ct desc")     ->limit($limit)     ->columns(array(          "b.*",          "ct" => "count(*)"          )); 

to read more, please, go zend_db_select documentation.

i hope helps.