Zend Framework 2 - Join tables in different databases and schemas

Question

In Zend Framework 2 when I want to make a join between different database tables I have to use the \Zend\Db\Sql\TableIdentifier class to avoid an incorrect escaping.

If I do in this way:

$select->join(['B' => 'database2.table2'], 'A.id = B.id');

It is rendered as:

SELECT [..] FROM "table" as "A" INNER JOIN "database2.table2" ON [...]

This causes an incorrect quoting "database2.table2"

To solve this situation I can do:

$tbl2 = new \Zend\Db\Sql\TableIdentifier('table2', 'database2');
$select->join(['B' => $tbl2], 'A.id = B.id');

In this way the quoting is correct "database2"."table2"

But how can I do if I have to specify the database and also the schema? (for example in ms sql server)

The desired result is "database2"."dbo"."table2"

Answer 1

When you're in a method of a table gateway class, you can do this:

$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);

If you try to write database.table literally, you don't let Zend to know that a database.table is what it is. Therefore, Zend will put the current database defined in the adapter that is used from current table gateway factory from Module.php, writing current_database.database.table.

This question has the same subject the zf2 make a join between two different databases

Answer 2

The schema is specified in your Zend\Db\Adapter\Adapter('database' => 'schemaname'). I think that you can not change the schema by query. You must take a diferent Adapter.

Answer 3

This worked for me. Should also work for you. You have to use it with tableGateway

use use Zend\Db\Sql\Select;
use Zend\Db\Sql\Where;

$someCondition=new Where();
$someCondition->equalTo('columnName',$columnValue);
//you can build $this->tableGateway from your DB adapter
$rowset = $this->tableGateway->select(function (Select $select) use ($someCondition) {
        $table2forInnerJoin = new \Zend\Db\Sql\TableIdentifier('table2Name', 'table2Database');
        $select->join(array('table2Name'=>$table2forInnerJoin),"table1Name.id = table2Name.id");
        $select->where($someCondition);
});
return $rowset;