MySQL Query to a Zend DB Select

Question

I have a MySQL standard query that I need to convert into a Zend_Db_Select but I can't get it to work.

I get this error:

Select query cannot join with another table

Here's the query:

// THE COUNTER
$subselect = $this->table->select()->from(
        array('x' => 'blog_comments'),
        array('x.post_id', new Zend_Db_Expr('count(*) as comments')))
    ->group('post_id');
// THE TOTAL SELECT
$select->from(array('p' => 'blog_posts'), array('p.*'))
       ->setIntegrityCheck(false)
       ->joinLeft(array(
           'x' => $subselect,
           'x.post_id = p.id', 
           array()
           )
       );

If someone can convert this, it would be great because I need that in select() mode because I use Zend_Pagination.

For those who want the full PHP function: Pastebin and the stack traces: Pastebin.

Answer 1

You may need: setIntegrityCheck(false) - review: http://framework.zend.com/manual/1.12/en/zend.db.select.html for more information

$select = $this->select()
->from(params) 
->setIntegrityCheck(false) 
->joinLeft(params)
->where(params);