Zf2 makes a connection between two different databases

Question

I am trying to make a connection between two tables hosted on different databases with Zend Framework 2.

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

The second table is called a hierarchy and is stored in the client database

I load database 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 to UserDao using 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 generates this SQL statement:

SELECT "users". *, "h". "id" AS "hierarchyId", "h". "level" AS "level" FROM "users Initials INNER JOIN" 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 am trying to specify an int join database name as follows:

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

But this also raises this exception:

SQLSTATE [42S02]: No base table or view found: 1146 Table "admin.customer.hierarchies" does not exist

I found this website where I explained how I can do this, but it is only valid for Zend Framework 1, and I am working with Zend Framework 2.

Using different databases with the Zend Framework

Can someone help me? You are welcome.

Thanks!

Answer 1

It looks like this question was asked some time ago, but I seem to have found a suitable solution or solution. If you use Zend \ Db \ Sql \ TableIdentifier and Zend \ Db \ Sq \ Expression, you can work around your problem.

 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 did not know in which database the table of your hierarchies is located, therefore I used "admin". You can replace it with which the database name has always been. See if this works for you, it seems to work well for me.

Answer 2

The structure does not support a connection to another database. You must use plain SQL to build the query.

Answer 3

The problem is that the Select class excludes quotation marks.

$ select-> join ("database2.table2", "database2.table2.id = table.id")

Displayed as:

SELECT 'table'. * 'database2.table2'. * FROM 'table' INNER JOIN 'database2.table2' ON 'database2'. 'table2'. 'id' = 'table'. 'id'

Note the inconsistent and incorrect quote around "database2.table2".

Updating lines 596, 599, 624, 625 to \ Zend \ Db \ Sql \ Choose quotiIdentifier to replace the method using the parameter "quoteIdentifierInFragment" correctly displays the query and allows you to cross-connect the database.

I sent a problem report to Zend because I don’t think the current behavior is intended, so I hope it will be updated in a future build. At the moment, this is easy enough (although admittedly a bit messy) to update the class manually.

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

Answer 4

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

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

to

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

and line 680 of

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

to

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

Obs: These are only those lines that I could identify, and cannot be a complete list.