zf2 make a join between two different databases

Question

I am trying to make a join between two tables placed in different databases with Zend Framework 2.

The first table is called users and is stored in the database admin

The second table is called hierarchy and is stored in the database customer

I load the databases adapters in global.php

return array(
'admin' => array(
    'driver' => 'Pdo',
    'dsn' => 'mysql:dbname=admin;host=localhost',
    'driver_options' => array(
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
    ),
),
'customer' => array(
    'driver' => 'Pdo',
    'dsn' => 'mysql:dbname=customer;host=localhost',
    'driver_options' => array(
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
    ),
),
'service_manager' => array(
    'factories' => array(
        'Zend\Db\Adapter\Adapter'
        => 'Zend\Db\Adapter\AdapterServiceFactory',
    ),
),

);

But when I try to make a join in UserDao with this function:

public function getSelect(Hierarchy $hierarchy) {
    $select = $this->tableGateway->getSql()->select();
    $select->where(array('level' => $hierarchy()->getId()));
    $select->join(array('h' => 'hierarchies'), 'h.id = users.idHierarchy', array('hierarchyId' => 'id', 'level' => 'level'));
    return $select;
}

This generate this SQL sentence:

SELECT "users".*, "h"."id" AS "hierarchyId", "h"."level" AS "level" FROM "users" INNER JOIN "hierarchies" AS "h" ON "h"."id" = "users"."idHierarchy" WHERE "level" = '1'

But it throws this exception when I try to use it:

Zend\Db\Adapter\Exception\InvalidQueryException
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'admin.hierarchies' doesn't exist

I try to indicate the name of the database int the join like this:

$select->join(array('h' => 'customer.hierarchies'), 'h.id = users.idHierarchy', array('hierarchyId' => 'id', 'level' => 'level'));

But it throws this exception too:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'admin.customer.hierarchies' doesn't exist

I found this web where explained how I can do it, but it's only valid for Zend Framework 1 and I'm working with Zend Framework 2.

Using Different Databases with Zend Framework

Could somebody help me? Please.

Thanks!

xangxiong · Answer 1 · 2013-08-08T02:47:01.553

Looks like this question was asked a while back, but I seem to have found a good workaround or solution. If you utilize the Zend\Db\Sql\TableIdentifier and Zend\Db\Sq\Expression, you will be able to get around your issue.

public function getSelect(Hierarchy $hierarchy) {
    $select = $this->tableGateway->getSql()->select();
    $select->where(array('level' => $hierarchy()->getId()));
    $select->join(
         array('h' => new TableIdentifier('hierarchies', 'admin')), 
         new Expression('h.id = ?', 'users.idHierarchy', Expression::TYPE_IDENTIFIER), 
         array('hierarchyId' => 'id', 'level' => 'level')
    );
    return $select;
}

I wasn't sure which database your hierarchies table is in so I used 'admin' for now. YOu can replace it with which ever database name you have. See if it works for you, seems to work nicely for me.

score 0 · Answer 2 · answered Apr 08 '13 at 17:23

0

The framework does not support a join with another database. You have to use plain SQL for building the query.

answered Apr 08 '13 at 17:23

giftnuss

529
2
9

score 0 · Answer 3 · answered Apr 24 '13 at 13:38

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

Paamand · Answer 4 · 2013-12-02T11:52:16.973

As Dan has answered, but for Zend 2.3 you should change line 742 in file Zend\Db\Sql\Select.php from

$joinName = $platform->quoteIdentifier($joinName);

to

$joinName = $platform->quoteIdentifierInFragment($joinName);

and line 680 from

$name = $platform->quoteIdentifier($name);

to

$name = $platform->quoteIdentifierInFragment($name);

Obs: These are only the lines that I could identify, and may not be a complete list.

score 0 · Answer 5 · answered Jun 13 '20 at 06:00

Why you're using DAO concept? Only in a table gateway you'll have all of you'll need. You already have adapters in global or local.php. You already have factories for table gateways, haven't you? Why you need to pass Hierarchy class (I think it is another table gateway) to the current class? To do the join that you want, the only thing that you need is table identifier.

$table2 = new Zend\Db\Sql\TableIdentifier('table2', 'schema_name'); 
$select = $this->tableGateway->getSql()->select()
->join($table2, 'table1.field = table2.field', ['fields_from_table2'], 'INNER');

$sql = new Sql($this->tableGateway->getAdapter());
$selectString = $sql->buildSqlString($select);
$result = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);

See the answer for this question

zf2 make a join between two different databases

5 Answers5

Linked