Laravel 中的多数据库关联关系

belongsToMany relationship in Laravel across multiple databases

提问by user1952811

I have model Aand model Bwhich lie in two different databases.

我有位于两个不同数据库中的模型A和模型B。

Now I have a pivot_table called a_bsin the same database as model A.

现在我a_bs在与 model 相同的数据库中调用了一个 pivot_table A。

I've setup the belongsToMany relatinoship like this in model A

我已经在模型中设置了这样的belongsToMany relatinoship A

public function bs()
{
    return $this->belongsToMany('B', 'a_bs', 'a_id', 'b_id');
}

When I try to access this relationship like so:

当我尝试像这样访问这种关系时：

$a = A::find($id);
print_r($a->bs->lists('id'));

I get an error that my pivot table doesn't exist in model B's database. Which is obviously correct since the pivot table is in model A's database. How can I let Laravel know that?

我收到一个错误，我的数据透视表不存在于模型 B 的数据库中。这显然是正确的，因为数据透视表位于模型 A 的数据库中。我怎么能让 Laravel 知道呢？

Do not suggest to put the pivot table in model B's database

不建议将数据透视表放在模型B的数据库中

回答by kmuenkel

Very simply:

很简单：

public function bs()
{
    $database = $this->getConnection()->getDatabaseName();
    return $this->belongsToMany('B', "$database.a_bs", 'a_id', 'b_id');
}

I'm obtaining the database name dynamically because my connection is configured based off an environment variable. Laravel seems to assume the pivot table to exist in the same database as the target relation, so this will force it to look instead to the database corresponding to the model that this method is in, your 'A' realm.

我正在动态获取数据库名称，因为我的连接是基于环境变量配置的。Laravel 似乎假设数据透视表与目标关系存在于同一数据库中，因此这将迫使它转而查找与此方法所在的模型相对应的数据库，即您的“A”领域。

---

---

If you're not worried about SQLite databases, i.e. in the scope of a unit-test, that's all you need. But if you are, keep reading.

如果您不担心 SQLite 数据库，即在单元测试的范围内，这就是您所需要的。但如果你是，请继续阅读。

---

---

Firstly, the previous example isn't sufficient on its own. The value of $database would end up being a file-path, so you need to alias it to something that won't break an SQL statement, and make it accessible to the current connection. "ATTACH DATABASE '$database' AS $name"is how you do that:

首先，仅靠前面的示例是不够的。$database 的值最终将成为一个文件路径，因此您需要将其别名为不会破坏 SQL 语句的内容，并使其可被当前连接访问。 "ATTACH DATABASE '$database' AS $name"是你如何做到这一点：

public function bs()
{
    $database = $this->getConnection()->getDatabaseName();
    if (is_file($database)) {
        $connection = app('B')->getConnection()->getName();
        $name = $this->getConnection()->getName();
        \Illuminate\Support\Facades\DB::connection($connection)->statement("ATTACH DATABASE '$database' AS $name");
        $database = $name;
    }
    return $this->belongsToMany('B', "$database.a_bs", 'a_id', 'b_id');
}

Warning: Transactions muck this up:If the current connection is using transactions, the ATTACH DATABASE statement will fail. You canuse transactions on it afterexecuting that statement though.

警告：事务搞砸了：如果当前连接正在使用事务，则 ATTACH DATABASE 语句将失败。不过，您可以在执行该语句后对其使用事务。

Whereas, if the relatedconnection uses transactions, the resulting data will be silently rendered invisible to the current one. This drove me nuts for longer than I'd care to admit, because my queries ran without error, but kept coming up empty. It seems only data truly written to the attached database is actually accessible to the one it's attached to.

然而，如果相关连接使用事务，则生成的数据将被静默地呈现为当前数据不可见。这让我发疯的时间比我愿意承认的要长，因为我的查询没有错误地运行，但一直是空的。似乎只有真正写入附加数据库的数据才能真正被它所附加的数据库访问。

So, after being forced to write to your attached database, you may still want your test to clean up after itself. A simple solution there would be to just use $this->artisan('migrate:rollback', ['--database' => $attachedConnectionName]);. But if you have multiple tests that need the same tables, this is not very efficient, as it forces them to have to rebuild them each time.

因此，在被迫写入附加数据库后，您可能仍希望测试自行清理。一个简单的解决方案就是使用$this->artisan('migrate:rollback', ['--database' => $attachedConnectionName]);. 但是如果你有多个测试需要相同的表，这不是很有效，因为它迫使他们每次都必须重建它们。

A better option would be to truncate the tables, but leave their structure in tact:

更好的选择是截断表格，但保留其结构：

//Get all tables within the attached database
collect(DB::connection($database)->select("SELECT name FROM sqlite_master WHERE type = 'table'"))->each(function ($table) use ($name) {
        //Clear all entries for the table
        DB::connection($database)->delete("DELETE FROM '$table->name'");
        //Reset any auto-incremented index value
        DB::connection($database)->delete("DELETE FROM sqlite_sequence WHERE name = '$table->name'");
    });
}

This will wipe all data from that connection, but there's no reason you couldn't apply some kind filter to that however you see fit. Alternatively, you could take advantage of the fact that SQLite DBs are easily-accessible files, and just copy the attached one to a temp file, and use it to overwrite the source after the test is done executing. The result would be functionally identical to a transaction.

这将擦除该连接中的所有数据，但您没有理由不能对它应用某种过滤器，但您认为合适。或者，您可以利用 SQLite DB 是易于访问的文件这一事实，只需将附加的文件复制到临时文件，并在测试执行完成后使用它来覆盖源。结果在功能上与交易相同。

回答by Arcadas

You can set the database of the table in the model class:

可以在model类中设置表的数据库：

protected $table = 'A.a_s';

And You have to use singular form when create a pivot table.

并且在创建数据透视表时必须使用单数形式。

/app/model/A.php

/应用程序/模型/A.php

class A extends Eloquent {

  // Set table name (plural) with database name
  protected $table = 'A.a_s';

  // Many to many relation
  public function b_s() {
    return $this->belongsToMany('B');
  } 
}

/app/model/B.php

/app/model/B.php

class B extends Eloquent {

  // Set table name (plural) with database name
  protected $table = 'B.b_s';

}

Query

询问

print_r(A::with('b_s')->where('id', 1)->get()->toArray());

MySQL

MySQL

CREATE TABLE `A`.`a_s` (
   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`id`)
);
CREATE TABLE `B`.`b_s` (
   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`id`)
);
CREATE TABLE `A`.`a_b` (
  `a_id` int(10) unsigned NOT NULL,
  `b_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`a_id`,`b_id`)
) ENGINE=InnoDB;

INSERT INTO A.a_s VALUES (NULL);
INSERT INTO A.a_s VALUES (NULL);
INSERT INTO B.b_s VALUES (NULL);
INSERT INTO A.a_b VALUES (1,1);
INSERT INTO A.a_b VALUES (1,2);

回答by AturSams

If the databases/schemas are on the same host server like @NiRR said just do this to override the default schema of the second connection:

如果数据库/模式在同一个主机服务器上，就像@NiRR 所说的那样，只需执行此操作即可覆盖第二个连接的默认模式：

return $this->belongsToMany('B', 'real-schema-name.a_bs');

or alternatively

或者

return $this->belongsToMany('A', 'real-schema-name.a_bs');

Depending on which model (A or B) is defined with the connection that isn't using the default schema.

取决于使用不使用默认架构的连接定义的模型（A 或 B）。

Remember that it's impossibleto do a join query that spans across two servers; which server is it going to get executed on? Each is missing some of the needed data needed to preform the request.

请记住，跨两台服务器进行连接查询是不可能的；它将在哪个服务器上执行？每个都缺少一些执行请求所需的数据。

回答by NiRR

This is only possible if the two databases are on the same connection (server).

仅当两个数据库位于同一连接（服务器）上时才可能这样做。

Its not possible to do this on two different connections (servers) since you'll need all three tables on a single server that will perform the join command.

不可能在两个不同的连接（服务器）上执行此操作，因为您需要在将执行 join 命令的单个服务器上的所有三个表。

回答by KYONKOPA

Explicitly setting or defining the connection for both models works just fine.

为两个模型显式设置或定义连接工作得很好。

protected $connection = 'connection1'; //inside model A
protected $connection = 'connection2'; //inside model B

EDIT

编辑

When Laravel is fetching a model from the DB, provided a database/connection has been defined (as a property) within the model, Laravel will use the database name for that connection when constructing the SQL. So when working with multiple connections, it is best to define the connection for every model.

当 Laravel 从数据库中获取模型时，如果在模型中定义了数据库/连接（作为属性），Laravel 将在构建 SQL 时使用该连接的数据库名称。因此，在使用多个连接时，最好为每个模型定义连接。

回答by sumit

I did it in a following way

我是按照以下方式做的

---

---

Defined the constants on env

在 env 上定义常量

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=system
DB_USERNAME=root
DB_PASSWORD=1234

My database.php looks like below

我的 database.php 如下所示

'connections' => [


        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'strict' => false,
            'engine' => null,
        ],

        'tenant' => [
                    'driver'   => 'mysql',
                    'host'     => 'localhost',
                    'database' => '',
                    'username' => 'root',
                    'password' => '1234',
                    'prefix'   => '',
                    'charset' => 'utf8',
                    'collation' => 'utf8_unicode_ci',
                    'prefix' => '',
                    'strict' => false,
                    'engine' => null,
                    ],



    ],


    'migrations' => 'migrations',

    ],
  ];

I setup tenant database like below

我设置租户数据库如下

public static function SetTenantDatabase(){

        //get company name from session

        $company=Company::findOrFail(Session::get('selected_company_id'));
        Config::set('database.connections.tenant.host', 'localhost');
        Config::set('database.connections.tenant.username', 'root');
        Config::set('database.connections.tenant.password', '1234');

        Config::set('database.connections.tenant.database',$company->database);
        DB::reconnect('tenant');
    }

I have one table 'security_group' on tenant database and one table 'user' at master database , and I have one table security_group_user on master database with many to many relationships between user and security group.

我在租户数据库上有一张表“security_group”，在主数据库上有一张表“user”，我在主数据库上有一张表 security_group_user ，用户和安全组之间存在多对多的关系。

My user model looks like below

我的用户模型如下所示

class User extends Authenticatable
{

    public function SecurityGroup() {
        return $this->belongsToMany('App\SecurityGroup', env('DB_DATABASE').'.security_group_user', 'user_id', 'security_group_id');
    }

}

My SecurityGroup model looks like below

我的 SecurityGroup 模型如下所示

class SecurityGroup extends Model
{
     protected $connection = 'tenant'; 
}

And I did the following in user controller

我在用户控制器中做了以下操作

$user->save();
//attach security group 
               $security_group=$input['security_group_id'];
               if(is_array($security_group)){
                   foreach($security_group as $key=>$val){
                        $user->SecurityGroup()->attach($val,['company_id' => Session::get('selected_company_id')]);

                   }
               }