Zend Framework 2 - Subqueries (Subselect) and Table Gateway

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 ) );

            $select->columns(
                array(
                    'id',
                    'title',
                    'ytlink',
                    '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 :)