To solve this problem I overloaded method Zend\Db\Sql\Select.
For your example of a complex query, I proceed as follows:
Your query : select * from tbl_test inner join (select * from tbl_test 1) x on 1=1 where test_id is not null group by test_id
My query : SELECT * FROM (select * from tbl_test inner join (select * from tbl_test 1) x on 1=1 where test_id is not null group by test_id) tmp WHERE test_id=1;
If your complex query use alias tmp then change in the method renderTable().
Here is how my class is used (change the namespace as need):
use SbmPdf\Model\Db\Sql\Select;
$sql = "select * from tbl_test inner join (select * from tbl_test 1) x on 1=1 where test_id is not null group by test_id";
$select = new Select($sql);
$select->where(array('id' => 1));
Here is my class (change the namespace as need):
namespace SbmPdf\Model\Db\Sql;
use Zend\Db\Adapter\Driver\DriverInterface;
use Zend\Db\Adapter\ParameterContainer;
use Zend\Db\Adapter\Platform\PlatformInterface;
use Zend\Db\Sql\Select as ZendSelect;
class Select extends ZendSelect
{
private $recordSource;
public function __construct($recordSource = null)
{
parent::__construct();
$this->recordSource = $recordSource;
}
public function setRecordSource($recordSource)
{
$this->recordSource = $recordSource;
return $this;
}
protected function processSelect(PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $parameterContainer = null)
{
$expr = 1;
if (empty($this->recordSource)) {
list ($table, $fromTable) = parent::resolveTable($this->table, $platform, $driver, $parameterContainer);
} else {
list ($table, $fromTable) = $this->resolveTable($this->recordSource, $platform);
}
$columns = array();
foreach ($this->columns as $columnIndexOrAs => $column) {
if ($column === self::SQL_STAR) {
$columns[] = array(
$fromTable . self::SQL_STAR
);
continue;
}
$columnName = $this->resolveColumnValue(array(
'column' => $column,
'fromTable' => $fromTable,
'isIdentifier' => true
), $platform, $driver, $parameterContainer, (is_string($columnIndexOrAs) ? $columnIndexOrAs : 'column'));
if (is_string($columnIndexOrAs)) {
$columnAs = $platform->quoteIdentifier($columnIndexOrAs);
} elseif (stripos($columnName, ' as ') === false) {
$columnAs = (is_string($column)) ? $platform->quoteIdentifier($column) : 'Expression' . $expr ++;
}
$columns[] = (isset($columnAs)) ? array(
$columnName,
$columnAs
) : array(
$columnName
);
}
foreach ($this->joins as $join) {
$joinName = (is_array($join['name'])) ? key($join['name']) : $join['name'];
$joinName = parent::resolveTable($joinName, $platform, $driver, $parameterContainer);
foreach ($join['columns'] as $jKey => $jColumn) {
$jColumns = array();
$jFromTable = is_scalar($jColumn) ? $joinName . $platform->getIdentifierSeparator() : '';
$jColumns[] = $this->resolveColumnValue(array(
'column' => $jColumn,
'fromTable' => $jFromTable,
'isIdentifier' => true
), $platform, $driver, $parameterContainer, (is_string($jKey) ? $jKey : 'column'));
if (is_string($jKey)) {
$jColumns[] = $platform->quoteIdentifier($jKey);
} elseif ($jColumn !== self::SQL_STAR) {
$jColumns[] = $platform->quoteIdentifier($jColumn);
}
$columns[] = $jColumns;
}
}
if ($this->quantifier) {
$quantifier = ($this->quantifier instanceof ExpressionInterface) ? $this->processExpression($this->quantifier, $platform, $driver, $parameterContainer, 'quantifier') : $this->quantifier;
}
if (! isset($table)) {
return array(
$columns
);
} elseif (isset($quantifier)) {
return array(
$quantifier,
$columns,
$table
);
} else {
return array(
$columns,
$table
);
}
}
protected function resolveTable($table, PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $parameterContainer = null)
{
$alias = null;
if (is_array($table)) {
$alias = key($table);
$table = current($table);
} else {
$alias = 'tmp';
}
$fromTable = $platform->quoteIdentifier($alias);
$table = $this->renderTable($table, $fromTable, false);
if ($alias) {} else {
$fromTable = $table;
}
if ($this->prefixColumnsWithTable && $fromTable) {
$fromTable .= $platform->getIdentifierSeparator();
} else {
$fromTable = '';
}
return array(
$table,
$fromTable
);
}
protected function renderTable($table, $alias = null, $parent = true)
{
if ($parent) {
return parent::renderTable($table, $alias);
} else {
if (empty($alias)) {
$alias = 'tmp';
}
return "($table) AS $alias";
}
}
}