Question: In ZF1 it worked like this:

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

```

How to do this in the ZF2? Thanks.

Answer: 
You can try this:

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

```

the output will be something like this:

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

```

I haven't figured out a nice way of using the query builder to perform these kind of queries without having to use raw queries.
You can try restructuring your SQL to something like:

```
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

```

Which you can represent using Zend Framework 2 as:

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

```

Assuming $dbh is your database adapter.