Zend 2.1: Is it possible to do a multiple schema join via Db>Sql>Select

Question

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

error that is returned is 'Statement couldn't be produced with sql', checking the SQL returned with the error the issue is that the users table is on a different schema. So my problem is that I need to add the additional schema within the second join. Hoping this is possible without resorting to raw SQL — tomo661, Apr 18 '13 at 08:23
Appears to be similar to this question -> http://stackoverflow.com/questions/15830774/zf2-make-a-join-between-two-different-databases and the lack of answers other than not possible suggests raw sql is the way to go — Crisp, Apr 18 '13 at 08:47
@Crisp yeah I'm coming to the conclusion now that it isn't possible, was just worried that I had missed a part of the puzzle. Thanks — tomo661, Apr 18 '13 at 11:28

score 1 · Answer 1 · answered Apr 18 '13 at 16:27

1

Looks like this isn't actually possible using Db\Select sadly. However you can create some nice vendor portable sql straight from the adapter. http://framework.zend.com/manual/2.1/en/modules/zend.db.adapter.html

Thanks.

answered Apr 18 '13 at 16:27

tomo661

88
4

Dan · Accepted Answer · 2013-04-24T13:41:37.817

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.

https://github.com/zendframework/zf2/issues/4307

nice fix although an edit to the core zend php will mean no easy updating in the future, the fix works well. It could be that this indeed an oversight that Zend might want to fix, good to flag it up to them. — tomo661, Apr 24 '13 at 13:41

Zend 2.1: Is it possible to do a multiple schema join via Db>Sql>Select

2 Answers2