Zend Framework 2: SQL子查询

Question

在ZF1中，它的工作方式如下：

 $selectColumns = array(
   '*',
   'orders_total' => "(".$db->select()->from("orders", array("COUNT(*)"))->where("orders.parent_id=mytable.id").")",
 );
 $select = $db->select()->from('mytable', $selectColumns);

如何在ZF2中实现？谢谢。

- Eremite

Answer 1

请尝试这个。

$sql = new Sql($this->_adapter);
$mainSelect = $sql->select()->from('mytable');
$subQry = $sql->select()
        ->from('orders')
        ->columns(array('orderCount' => new \Zend\Db\Sql\Expression('COUNT(orders.id)')));
$mainSelect->columns(
        array(
            'id', 
            'orders_total' => new \Zend\Db\Sql\Expression('?', array($subQry)),
        )
);

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

return $resultSet->toArray();

链接: ZF2 - 子查询

- Anjith K P

Answer 2

你可以尝试这个：

// Make your query here using the builder if you wish, 
// but we will need to convert to string for the Expression
$sub = new Select('orders');
$sub->columns(array(new Expression('COUNT(*) as total')))
    ->where(array('id' => 4))
;
// You could just create an expression..
$subquery = new \Zend\Db\Sql\Expression("({$sub->getSqlString()})");

$select = new \Zend\Db\Sql\select('tablename'); // this is inside a 

$select->columns(array('*', 'orders_total' => $subquery));

输出将会是这样的：

SELECT 
    .*, 
    (SELECT COUNT(*) as total FROM "orders" WHERE "id" = '4') AS orders_total 
FROM tablename

我还没有找到一种好的方法，可以使用查询生成器执行这些类型的查询，而不必使用原始查询。

- Andrew

Answer 3

如果没有更多的信息，你可以尝试：

 $selectColumns = array(
    '*',
    'orders_total' => "(".$db->select()->from("orders", array("COUNT(*)"))->where("orders.parent_id", "mytable.id").")",
);
 $select = $db->select()->from('mytable', $selectColumns);

您需要在顶部添加使用语句：

use Zend\Db\Sql\Select;
use Zend\Db\Sql\Where;

- Shaun Freeman

Answer 4

您可以尝试重构您的SQL语句，例如：

SELECT
    *,
    SUM(IF(O.parent_id IS NULL, 0, 1)) AS orders_total
FROM mytable
    LEFT JOIN orders O ON mytable.id = O.parent_id

你可以使用Zend Framework 2来表示：

$select = new Select('mytable');
$select->columns(array(
    '*',
    'orders_total' => new Expression("SUM(IF(O.parent_id IS NULL, 0, 1))")
));
$select->join(array('O', 'orders'), 
              "mytable.id = O.parent_id",
              Select::JOIN_LEFT);   
$select->group(array('mytable.id'));

$result = $dbh->selectWith($select);

假设$dbh是您的数据库适配器。

- xangxiong