Zend 框架 2:子查询 [英] Zend Framework 2: subqueries

问题描述

ZF2 最近更新到 2.1.4 版，数据库 API 已更改 http://framework.zend.com/security/advisory/ZF2013-03

现在我用于子查询的代码是一个例外:

$sub = new Select('md_type');$sub->columns(array(new Expression('COUNT(mt2.parent_id) as total')))->哪里(数组(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() ------> 注意:在没有扩展/驱动程序支持的情况下尝试引用 Zend\Db\Adapter\Platform\Mysql 中的值可能会在生产环境中引入安全漏洞.

我找不到使用子查询的替代方法.请告诉我现在如何，如何使用子查询.谢谢！

<小时>

这是错误:https://github.com/zendframework/zf2/pull/4068

在不久的将来，我认为它会修复.

解决方案

请试试这个.

$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(大批('ID'，'总计' =>new \Zend\Db\Sql\Expression('?', array($subQry)),));$statement = $sql->prepareStatementForSqlObject($mainSelect);$comments = $statement->execute();$resultSet = 新的结果集();$resultSet->初始化($comments);返回 $resultSet->toArray();

参考:ZF2 - 子查询

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.

解决方案

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

这篇关于Zend 框架 2:子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！