What is the Laravel syntax to join 2 tables from different databases in postgres?
For example:
database1 table column
join
database2 table column
-
What is your problem here? What you have already done?Oleg Butuzov– Oleg Butuzov2019-12-20 07:23:11 +00:00Commented Dec 20, 2019 at 7:23
Add a comment
|
1 Answer
You can do it like this:
Configure:
Define the config/database.php multiple databases' connection:
'pg' => [
'driver' => 'pgsql',
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
...
];
'pg_2' => [
'driver' => 'pgsql',
'host' => env('DB_HOST_ONLINE', '127.0.0.1'),
'port' => env('DB_PORT_ONLINE', '3306'),
'database' => env('DB_DATABASE_ONLINE', 'forge'),
'username' => env('DB_USERNAME_ONLINE', 'forge'),
];
...
And set connection in Model:
class Model1 extends Model
{
protected $connection = 'pg';
}
class Model2 extends Model
{
protected $connection = 'pg_2';
}
Usage:
And you can easily get Eloquent or query builder from different databases:
Model2::join('database1_name.table AS table1', 'table1.id', '=', 'model2.table_id')
->where(...)
->select('model2.*', 'table1.column')
# OR
\DB::connection('pg_2')->table('model2')
->join('database1_name.table AS table1', 'table1.id', '=', 'model2.table_id')
->where(...)
->select('model2.*', 'table1.column')
