laravel BelongsTo 与不同数据库的关系不起作用

laravel BelongsTo relationship with different databases not working

提问by NiRR

I've seen in several places to "stay away" from this, but alas - this is how my DB is built:

我已经在几个地方看到过“远离”这个，但是唉 - 这就是我的数据库的构建方式：

class Album extends Eloquent {

   // default connection

   public function genre() {
       return $this->belongsTo('genre');
   }

and the Genre table:

和流派表：

class Genre extends Eloquent {
    protected $connection = 'Resources';

}

My database.php:

我的database.php：

'Resources' => array(
                    'driver'    => 'mysql',
                    'host'      => 'localhost',
                    'database'  => 'resources',
                    'username'  => 'user',
                    'password'  => 'password',
                    'charset'   => 'utf8',
                    'collation' => 'utf8_unicode_ci',
                    'prefix'    => '',
            ),

 'mysql' => array(
                    'driver'    => 'mysql',
                    'host'      => 'localhost',
                    'database'  => 'my_data',
                    'username'  => 'user',
                    'password'  => 'password',
                    'charset'   => 'utf8',
                    'collation' => 'utf8_unicode_ci',
                    'prefix'    => '',
            ),

and when I try to run

当我尝试跑步时

Album::whereHas('genre', function ($q) {
   $q->where('genre', 'German HopScotch'); 
});

it doesn't select properly (doesn't add the database name to the table "genres"):

它没有正确选择（不会将数据库名称添加到“流派”表中）：

Next exception 'Illuminate\Database\QueryException' with message 'SQLSTATE[42S02]: Base table or view not found: 1146 Table 'my_data.genres' doesn't exist

Its important to note that this works perfectly:

重要的是要注意这完美地工作：

Album::first()->genre;

Update

更新

The best I've found so far is to use the builder's "from" method to specifically name the correct connection. I've discovered that the builder inside the query can receive "from"

迄今为止我发现的最好的方法是使用构建器的“from”方法来专门命名正确的连接。我发现查询中的构建器可以接收“来自”

Album::whereHas('genre', function ($q) {
   $q->from('resources.genres')->where('genre', 'German HopScotch'); 
});

This is a decent solution but it requires me to dig in the database php and find a good way to get the proper table and database name from the relation 'genre'.

这是一个不错的解决方案，但它需要我在数据库 php 中挖掘并找到一种从关系“流派”中获取正确表和数据库名称的好方法。

I will appreciate if anyone else can build on this solution and make it more general.

如果其他人可以在此解决方案的基础上构建并使其更通用，我将不胜感激。

采纳答案by NiRR

This is my own solution and it works in general for me but its mega-complicated.

这是我自己的解决方案，它通常对我有用，但它非常复杂。

I'm using the builder "from" method to set the table and database correctly inside the subquery. I just need to pass the correct information inside.

我正在使用构建器“from”方法在子查询中正确设置表和数据库。我只需要在里面传递正确的信息。

Assume the subquery can be as complicated as "genres.sample" or even deeper (which means albums has a relation to genres, and genres has a relation to samples) this is how

假设子查询可以像“genres.sample”一样复杂甚至更深（这意味着专辑与流派有关，流派与样本有关），这就是

$subQuery = 'genres.samples';
$goDeep = (with (new Album));

$tableBreakdown =  preg_split('/\./', $subQuery); //  = ['genres', 'samples']

// I recurse to find the innermost table $album->genres()->getRelated()->sample()->getRelated()
foreach ($tableBreakdown as $table)
    $goDeep = $goDeep->$table()->getRelated();

// now I have the innermost, get table name and database name

$alternativeConnection =  Config::get("database.connections." . $goDeep->getConnectionName() . ".database"); // should be equal to the correct database name

$tableName = $goDeep->getTable(); // I have to use the table name in the "from" method below

Album::whereHas($subQuery, function ($q) use ($alternativeConnection, $tableName) {
$q->from("$alternativeConnection.$tableName"); 
$q->where(....... yadda yadda);
    });

tl:dr;

tl：博士；

Album::whereHas('genres', function ($q) { 
    $q->from('resources.genres')->where(....); 
});

回答by Sabyasachi Ghosh

Solution for laravel v5.7 and above

laravel v5.7及以上解决方案

class Album extends Eloquent {

   // default connection

   public function genre() {
       return $this->setConnection('Resources')->belongsTo('genre');
   }
...
}

回答by amflare

It looks like Eager Loadingwill do what you want to do

看起来Eager Loading会做你想做的事

Album::with(['genre' => function ($q) {
   $q->connection('Resources')
     ->where('genre', 'German HopScotch'); 
}]);

回答by Angel Santiago Jaime Zavala

I was facing the same issue on Laravel 5.6. On a Many-to-Manyscenario, and supposing the connection from my ModelAwas the default one, what I did was the following:

我在 Laravel 5.6 上遇到了同样的问题。在多对多场景中，假设来自我的连接ModelA是默认连接，我所做的如下：

1.- Prefix the schema name in the relationships:

1.- 在关系中添加模式名称的前缀：

// From ModelA and default connection (a.k.a connection1)
$this->belongsToMany('ModelB', 'schema.pivot-table-name');

// From ModelB and connection2
$this->belongsToMany('ModelA', 'schema.pivot-table-name');

2.- Overwrite connection parameter within the ModelBclass and alsospecify the schema as a prefix in the overwritten $tableattribute e.g.

2.-所述内覆盖连接参数ModelB类，并且还指定模式中被覆盖的前缀$table属性例如

protected $connection = 'connection2';

protected $table = 'connection2-schema-name.table';

3.- In case of requiring a custom behavior for the pivot table, what I did was just to implement the required model and specify it via the ->using('PivotModel');function on the models relationships (as stated in the documentation). Finally I did the same as in the point 2 above, but on the pivot model

3.- 如果需要为数据透视表自定义行为，我所做的只是实现所需的模型并通过->using('PivotModel');模型关系上的函数指定它（如文档中所述）。最后我做了与上面第 2 点相同的操作，但是在枢轴模型上

I haven't tried it yet, but I guess the same can be done for other kind of relationships, at least for the basic ones (One-to-One, One-to-Many, etc)

我还没有尝试过，但我想对于其他类型的关系也可以这样做，至少对于基本的关系（一对一、一对多等）

回答by Carlos Escobar

Add the connection variable with the default connection on the genre model:

在流派模型上添加具有默认连接的连接变量：

protected $connection = 'mysql';

I had some problems with the relationships by not adding this.

通过不添加这个，我在关系上遇到了一些问题。

回答by Alex

This is the way it worked for me:

这是它对我有用的方式：

In my .env and config/database.php i have defined my other connection => How to use multiple databases in Laravel

在我的 .env 和 config/database.php 中，我定义了我的其他连接 =>如何在 Laravel 中使用多个数据库

I updated my model this way:

我以这种方式更新了我的模型：

class MyOtherDBModel extends Model
{
    protected $table = 'tablename';
    protected $connection = 'mysql2';

    public function __construct(array $attributes = [])
    {
        $this->table = env('DB_DATABASE_2').'.'.$this->table;
        parent::__construct();
    }
}

class MyModel extends Model
{
    public function myOtherModel()
    {
        return $this->belongsTo(MyOtherDBModel::class, 'field', 'field');
    }
}

Now i can call

现在我可以打电话

$query = MyModel::whereHas('myOtherModel');

回答by Jamesking56

I found a really good article for this here: http://fideloper.com/laravel-multiple-database-connections

我在这里找到了一篇非常好的文章：http: //fideloper.com/laravel-multiple-database-connections

You basically have to specify your two connections in your config file like so:

您基本上必须在配置文件中指定两个连接，如下所示：

<?php
return array(

    'default' => 'mysql',

    'connections' => array(

        # Our primary database connection
        'mysql' => array(
            'driver'    => 'mysql',
            'host'      => 'host1',
            'database'  => 'database1',
            'username'  => 'user1',
            'password'  => 'pass1'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),

        # Our secondary database connection
        'mysql2' => array(
            'driver'    => 'mysql',
            'host'      => 'host2',
            'database'  => 'database2',
            'username'  => 'user2',
            'password'  => 'pass2'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),
    ),
);

So your two connections are aliased to mysqland mysql2.

因此，您的两个连接别名为mysql和mysql2。

Then you can tell eloquent which 'alias' to use like so:

然后你可以像这样告诉 eloquent 使用哪个“别名”：

<?php

class SomeModel extends Eloquent {

    protected $connection = 'mysql2';

}

Then you can setup your relationships like normal.

然后你可以像往常一样建立你的关系。

tl;dr:Basically instead of specifying the table name as $connectionin eloquent, specify the connection alias in your configuration and it should work.

tl; dr：基本上不是像$connectioneloquent那样指定表名，而是在配置中指定连接别名，它应该可以工作。

回答by hawx

I had the same issue when relationship wasn't working off the model connection.

当关系在模型连接上不起作用时，我遇到了同样的问题。

My solution was to override the belongsToMany method on the model trying to establish. See example below.

我的解决方案是在试图建立的模型上覆盖belongsToMany 方法。请参阅下面的示例。

<?php

namespace App\Model;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;

class ConnectionModel extends Model
{
    /**
     * Override method to allow inheriting connection of parent
     *
     * Define a many-to-many relationship.
     *
     * @param  string  $related
     * @param  string  $table
     * @param  string  $foreignKey
     * @param  string  $otherKey
     * @param  string  $relation
     * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany|BelongsToMany
     */
    public function belongsToMany($related, $table = null, $foreignKey = null, $otherKey = null, $relation = null)
    {
        // If no relationship name was passed, we will pull backtraces to get the
        // name of the calling function. We will use that function name as the
        // title of this relation since that is a great convention to apply.
        if (is_null($relation)) {
            $relation = $this->getBelongsToManyCaller();
        }

        // First, we'll need to determine the foreign key and "other key" for the
        // relationship. Once we have determined the keys we'll make the query
        // instances as well as the relationship instances we need for this.
        $foreignKey = $foreignKey ?: $this->getForeignKey();

        $instance = new $related;

        // get connection from parent
        $instance->setConnection(parent::getConnectionName());

        $otherKey = $otherKey ?: $instance->getForeignKey();

        // If no table name was provided, we can guess it by concatenating the two
        // models using underscores in alphabetical order. The two model names
        // are transformed to snake case from their default CamelCase also.
        if (is_null($table)) {
            $table = $this->joiningTable($related);
        }

        // Now we're ready to create a new query builder for the related model and
        // the relationship instances for the relation. The relations will set
        // appropriate query constraint and entirely manages the hydrations.
        $query = $instance->newQuery();

        return new BelongsToMany($query, $this, $table, $foreignKey, $otherKey, $relation);
    }
}

回答by Jeremie Ges

To start change 'Resources' in database.phpby 'resources', will be better !

通过'resources'开始更改database.php中的'Resources'，会更好！

I'm curious, can you try that ?

我很好奇，你能试试吗？

Album::whereHas('genre', function ($q) {
   $q->setConnection('resources')->where('genre', 'German HopScotch'); 
});