Zend Framework 2: subqueries

php, zend-framework2

Solution

Please try this.

$sql = new Sql($this->_adapter);
$mainSelect = $sql->select()->from('mt1');
$subQry = $sql->select()
        ->from('md_type')
        ->columns(array('orderCount' => new \Zend\Db\Sql\Expression('COUNT(md_type.parent_id)')))
        ->where('mt2.parent_id = mt1.id');
$mainSelect->columns(
        array(
            'id', 
            'total' => new \Zend\Db\Sql\Expression('?', array($subQry)),
        )
);

$statement = $sql->prepareStatementForSqlObject($mainSelect);
$comments = $statement->execute();
$resultSet = new ResultSet();
$resultSet->initialize($comments);

return $resultSet->toArray();

Reference: ZF2 - subqueries

Problem

ZF2 recently updated to version 2.1.4 with a database API has changed http://framework.zend.com/security/advisory/ZF2013-03 Now the code that I used for a subquery was an exception: ``` $sub = new Select('md_type'); $sub->columns(array(new Expression('COUNT(mt2.parent_id) as total'))) ->where(array( new \Zend\Db\Sql\Predicate\Expression('mt2.parent_id = mt1.id') )) ; $subquery = new \Zend\Db\Sql\Expression("({$sub->getSqlString()})"); $select = new \Zend\Db\Sql\Select('mt1'); $select->columns(array('*', 'cnt' => $subquery)); ``` $sub->getSqlString() ------> Notice: Attempting to quote a value in Zend\Db\Adapter\Platform\Mysql without extension/driver support can introduce security vulnerabilities in a production environment. I can not find an alternative way to use subqueries. Please tell me how to be, how to use subqueries now. Thanks! It's bug: https://github.com/zendframework/zf2/pull/4068 In the near future, I think it fixed.

Original source