I'm pretty new to Zend and having a go with Zend Select abstraction. I have hit a bit of a problem when trying to create a query which joins another table from a different schema.
The mySql query I'm trying to produce is:
SELECT * FROM `assets`
JOIN `projects`
ON `assets`.project_id = `projects`.id
JOIN `secondSchema`.`users` AS `users`
ON `users`.id = `projects`.user_id
WHERE `projects`.id = ?
AND `users`.id = ?
working with Zend/Db/Sql/Sql/Select I can setup the following select builder, but I am struggling with setting another schema in the resulting Sql statement:
$select = $this->_sqlBuilder->select();
$select->from('assets');
$select->join(
'projects',
'projects.id = assets.project_id'
);
$select->join(
'users',
'users.id = projects.user_id'
);
$select->where->equalTo('assets.project_id', $projectID);
$select->where->equalTo('users.id', $userId);
I have tried using \Zend\Db\Sql\TableIdentifier to set the schema but the original schema in the adapter is then replaced, there doesn't look like a way to have both schemas present in the query.
Is there anyway to do this using Sql/Select or am I going about this the wrong way with Zend Db?
Thanks for the help
The issue is occurring because of the way the Select class escapes quotes.
$select->join("database2.table2", "database2.table2.id = table.id")
Is rendered as:
SELECT 'table'.*, 'database2.table2'.* FROM 'table' INNER JOIN 'database2.table2' ON 'database2'.'table2'.'id' = 'table'.'id'
Note the inconsistant and incorrect quoting around "database2.table2".
Updating lines 596, 599, 624, 625 in \Zend\Db\Sql\Select to replace the "quoteIdentifier" method with "quoteIdentifierInFragment" renders the query correctly and allows a cross database join to be performed.
I've submitted an issue report to Zend as I don't believe the current behaviour is intended so hopefully it will be updated in a future build. For now it's easy enough (though admittedly a little dirty) to update the class manually.