Nested Select with ZF2

Question

Trying to get a nested select using Zend\Db\Sql\Select and can't see anything at all in the documentation or on google.

Wanting to do something like this:

SELECT 
    table1.*,
    (SELECT x,y,z FROM table2 WHERE table2.a = table1.a) as b
FROM table1 

Without the nested select, it would look something like this:

$select = new Zend\Db\Sql\Select;
$select
 ->columns(array(
    '*'
 ))
 ->from('table1')

ZF1 looked about creating a subSelect item and then adding it as an Expression inside the list of columns but in ZF2 it complains about an Expression needing to be a string.

Edit: The nested-select needs to be as a column as I end up with multiplied rows when using GROUP BY on same column name. This is the correct query I'm trying to get into Zend\Db\Sql\Select:

SELECT
    users.id, 
    (SELECT count(explorations.id) FROM explorations WHERE user_id = users.id) as total_explorations, 
    count(villages.id)
FROM 
    users
INNER JOIN
    villages
        on (villages.user_id = users.id)
GROUP BY 
    users.id

Answer 1

Ralph Schindler has a repository of different DB patterns that he has specifically implemented in Zend\Db. Here's one for subselects: https://github.com/ralphschindler/Zend_Db-Examples/blob/master/example-20.php

The content is this:

<?php

/** @var $adapter Zend\Db\Adapter\Adapter */
$adapter = include ((file_exists('bootstrap.php')) ? 'bootstrap.php' : 'bootstrap.dist.php');
refresh_data($adapter);

use Zend\Db\Sql;
use Zend\Db\ResultSet\ResultSet;

$sql = new Sql\Sql($adapter);

$subselect = $sql->select();
$subselect->from('artist')
    ->columns(array('name'))
    ->join('album', 'artist.id = album.artist_id', array())
    ->where->greaterThan('release_date', '2005-01-01');


$select = $sql->select();
$select->from('artist')
    ->order(array('name' => Sql\Select::ORDER_ASCENDING))
    ->where
        ->like('name', 'L%')
        ->AND->in('name', $subselect);

$statement = $sql->prepareStatementForSqlObject($select);

$result = $statement->execute();

$rows = array_values(iterator_to_array($result));

assert_example_works(
    count($rows) == 2
    && $rows[0]['name'] == 'Lady Gaga'
    && $rows[1]['name'] == 'Linkin Park'
);

Basically, you can use one select as the value of the predicate of another select.