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