使用 Laravel Eloquent 连接同一服务器上不同数据库中的两个 MySQL 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41423603/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Join two MySQL tables in different databases on the same server with Laravel Eloquent
提问by Iman Sedighi
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 table1with a connection to database1and table2with 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？
回答by Iman Sedighi
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();
回答by rahul
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.
我已经在我的本地主机上尝试过它的工作。
回答by Mohammed Khalid Khan
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 配置中为表使用前缀。此外，如果您在其中一个模型上定义非默认连接，则需要为两者调整 $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.
当然，除非您有相同的架构，但无论如何这都不是您想要的。
回答by MM2
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);
}
}
