使用Laravel Eloquent在同一服务器上的不同数据库中加入两个MySQL表

I have two tables in two different databases. Both databases are hosted on same AWS RDS server. I have one user account which can access both databases. I defined two different connections in config\database.php:

我在两个不同的数据库中有两个表。两个数据库都托管在同一AWS RDS服务器上。我有一个用户帐户可以访问这两个数据库。我在config \ database.php中定义了两个不同的连接:

return array(
    'default' => 'mysql',
    'connections' => array(
        # Our primary database connection
        'mysql' => array(
            'driver'    => 'mysql',
            'host'      => 'samehost',
            'database'  => 'database1',
            'username'  => 'user1',
            'password'  => 'pass1'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),
        # Our secondary database connection
        'mysql2' => array(
            'driver'    => 'mysql',
            'host'      => 'samehost',
            'database'  => 'database2',
            'username'  => 'user2',
            'password'  => 'pass2'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),
    ),
);

I have two models for table1 with a connection to database1 and table2 with a connection to database2. Both tables have a column id. How to join queries with Eloquent models for the rows with the same id?

我有两个用于table1的模型,它们连接到database1,table2连接到database2。两个表都有一个列id。如何使用Eloquent模型为具有相同ID的行加入查询?

4 个解决方案

#1

This solution worked for me:

这个解决方案对我有用:

Model1::where('postID',$postID)
      ->join('database2.table2 as db2','Model1.id','=','db2.id')
      ->select(['Model1.*','db2.firstName','db2.lastName'])
      ->orderBy('score','desc')
      ->get();

#2

You can try in this way if you have both databases on the same connection and is set to default.

如果两个数据库位于同一连接上并且设置为默认值,则可以尝试这种方式。

$query = DB::table('database1.table1 as dt1')->leftjoin('database2.table2 as dt2', 'dt2.ID', '=', 'dt1.ID');        
$output = $query->select(['dt1.*','dt2.*'])->get();

I have tried on my localhost its working.

我试过我的localhost工作。

#3

It's tricky, but can be achieved. However there are some limitations, that may lead to raw solutions anyway.

这很棘手,但可以实现。但是有一些限制,无论如何都可能导致原始解决方案。

Here's what you need, assuming db1 is default:

假设db1是默认值,这就是你需要的:

// class ModelOne
public function modelTwo()
{
return $this->hasOne('ModelTwo', 'id');
}

//class ModelTwo
protected $table = 'db2.model_two_table';

public function modelOne()
{
return $this->belongsTo('ModelOne', 'id');
}
// then
$model1 = ModelOne::with('modelTwo')->get();
$model1 = ModelOne::has('modelTwo')->first(); 
// and so on

Mind that you can't use prefix for you tables in the db config. Also, if you define non-default connections on one of the models, then you need to adjust $table for both.

请注意,您不能在db config中为表使用前缀。此外,如果您在其中一个模型上定义非默认连接,则需要为两者调整$ table。

You can also use different connections for each model and many features will work just like that, however you can't rely on the joins that Eloquent builds:

您还可以为每个模型使用不同的连接,许多功能将像这样工作,但是您不能依赖Eloquent构建的连接:

ModelOne::with('modelTwo')->get(); // works as expected - this is what you asked for
ModelOne::has('modelTwo')->get(); // error, no table found

of course unless you have the same schema, but then it's not what you wanted anyway.

当然,除非你有相同的架构,但它不是你想要的。

#4

A simple eloquent way to connect two models of different databases

一种简单直接的方式来连接两个不同数据库的模型

class User extends Model {

  public function Company()
  {
    return $this->hasOne(Company::class);
  }
}

class Company extends Model {
  protected $connection = 'mysql2';

  public function User()
  {
    return $this->belongsTo(User::class);
  }
}

#1