This one can be a bit of a pain, and I had a few folks ask me how to do this, thought I'd share here and pass a link around.  The question takes many shapes!

How can I perform a subquery in ZF2?

How do I properly escape a subquery in my Table Gateway?

I'm trying to echo my query to debug it and am getting warning messages from ZF2?

It's not terribly difficult, but the docs are a bit piecemeal on the net.  I've tried to find links on StackOverflow to help as well, but haven't found any good ones.  Here goes - hope it helps!


The function in your AbstractTableGateway will look like such, pay attention to fetchAllWithVotes:

namespace Application\Model\Table;

use Zend\Db\Adapter\Adapter;
use Zend\Db\Adapter\AdapterAwareInterface;
use Zend\Db\Sql\Expression;
use Zend\Db\Sql\Select;
use Zend\Db\TableGateway\AbstractTableGateway;

class SongsTable extends AbstractTableGateway implements AdapterAwareInterface
    protected $table = 'songs';

    public function setDbAdapter( Adapter $adapter)
        $this->adapter = $adapter;

    public function fetchAllWithVotes( $user_id )
        $adapter   = $this->getAdapter();
        $resultset = $this->select( function( Select $select ) use ( $user_id, $adapter ) {

            $sub1   = new Select( 'votes' );
            $sub1->columns( array( 'total' => new Expression('COUNT(*)') ) )
                    ->where( array( 'id' => new Expression( 'song_id' ) ) );

            $sub2   = new Select( 'votes' );
            $sub2->columns( array( 'total' => new Expression('COUNT(*)') ) )
                    ->where( array( 'id' => new Expression( 'song_id' ) ) )
                        ->where( array( 'user_id' => $user_id ) );

                    'total_votes' => new \Zend\Db\Sql\Expression( '?', array( $sub1 ) ),
                    'your_votes' => new \Zend\Db\Sql\Expression( '?', array( $sub2 ) ),

            var_dump( $select->getSqlString( $adapter->getPlatform() ) );


        return $resultset->toArray();

Pretty self-explanatory - all the guts are there!  Hope this helps :)