Hello,
I came across a situation where I needed to join a table with a temporary table. I'm not sure if all databases support this feature, but MySQL does. At first, I tried joining a select object with another select object, but the resulting query ended up double-quoting my temp table's select:
$select = $table->select()
->setIntegrityCheck(false)
->from($table, '*')
->joinInner(
array(
'temp' => $table->select()
->from($table, array('id' => 'MAX(id)'))
->group('file_id')
),
'project_file.id =
temp.id',
array()
);
// Resulting SQL:
SELECT
`project_file`.*
FROM `project_file`
INNER JOIN `SELECT
MAX(id) AS ``id``
FROM ``project_file``
GROUP BY
``file_id``` AS `temp` ON project_file.id =
temp.id
But, I noticed that if I pass the return value of the select's__toString() method to a new instance of Zend_Db_Expr, I get valid (and desired) SQL:
$select = $table->select()
->setIntegrityCheck(false)
->from($table, '*')
->joinInner(
array(
'temp' => new Zend_Db_Expr('(' . $table->select()
->from($table, array('id' => 'MAX(id)'))
->group('file_id') . ')'
),
'project_file.id =
temp.id',
array()
);
// SQL:
SELECT
`project_file`.*
FROM `project_file`
INNER JOIN (SELECT
MAX(id) AS `id`
FROM `project_file`
GROUP BY
`file_id`) AS `temp` ON project_file.id =
temp.id
To simplify this, I've added the following lines to Zend/Db/Select.php at line 372 (right after the if/else blocks):
// Allow joining with subqueries if $tableName is a select object
if ($tableName instanceof Zend_Db_Select) {
require_once 'Zend/Db/Expr.php';
$tableName = new Zend_Db_Expr('(' . $tableName->__toString() . ')');
}
Would it be possible to get something like this added to the Zend core? Would there be any issues with databases that don't support subqueries?
