zf2 在两个不同的数据库之间建立连接 [英] zf2 make a join between two different databases

问题描述

我正在尝试使用 Zend Framework 2 在不同数据库中的两个表之间建立连接.

第一个表叫做users，存储在数据库admin

第二个表叫做hierarchy，存储在数据库customer

我在 global.php 中加载数据库适配器

<块引用>

返回数组('管理员' =>大批('司机' =>'Pdo','dsn' =>'mysql:dbname=admin;host=localhost','driver_options' =>大批(PDO::MYSQL_ATTR_INIT_COMMAND =>'设置名称\'UTF8\''),),'客户' =>大批('司机' =>'Pdo','dsn' =>'mysql:dbname=customer;host=localhost','driver_options' =>大批(PDO::MYSQL_ATTR_INIT_COMMAND =>'设置名称\'UTF8\''),),'service_manager' =>大批('工厂' =>大批('Zend\Db\Adapter\Adapter'=>'Zend\Db\Adapter\AdapterServiceFactory',),),

);

但是当我尝试使用此功能加入 UserDao 时:

<块引用>

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'));返回 $select;}

这会生成这个 SQL 语句:

<块引用>

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'

但是当我尝试使用它时它会抛出这个异常:

<块引用>

Zend\Db\Adapter\Exception\InvalidQueryExceptionSQLSTATE[42S02]:未找到基表或视图:1146 表admin.hierarchies"不存在

我尝试在连接中指示数据库的名称，如下所示:

<块引用>

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

但它也会抛出这个异常:

<块引用>

SQLSTATE[42S02]:未找到基表或视图:1146 表 'admin.customer.hierarchies' 不存在

我发现这个网站解释了我如何做到这一点，但它只对 Zend Framework 1 有效，我正在使用 Zend Framework 2.

在 Zend Framework 中使用不同的数据库

有人可以帮我吗?请.

谢谢！

解决方案

看起来这个问题在不久前有人问过，但我似乎找到了一个很好的解决方法或解决方案.如果您使用 Zend\Db\Sql\TableIdentifier 和 Zend\Db\Sq\Expression，您将能够解决您的问题.

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),数组('hierarchyId' => 'id', 'level' => 'level'));返回 $select;}

我不确定您的层次结构表在哪个数据库中，所以我现在使用admin".您可以将其替换为您拥有的任何数据库名称.看看它是否对你有用，对我来说似乎很好用.

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!

解决方案

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.

这篇关于zf2 在两个不同的数据库之间建立连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！