CODEWITHSUNDEEP
phplaravelpostgresqljoin
GDP
GDP

Reputation: 1

Join 2 or multiple tables from different database in postgres using PHP Laravel 2019

What is the Laravel syntax to join 2 tables from different databases in postgres?

For example:

database1 table column
join
database2 table column

Upvotes: 0

Views: 1142

Answers (1)

TsaiKoga
TsaiKoga

Reputation: 13394

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')

Upvotes: 1

Related Questions