CODEWITHSUNDEEP
phplaraveldatabasepsql
fractal5
fractal5

Reputation: 2132

Laravel - Join tables across 2 databases (same server)

I'm trying to join 2 tables in 2 databases. But using the common solutions don't seem to be working and I can't figure out why. Here is what I have:

My connections:

    'pgsql' => [
        'driver'   => 'pgsql',
        'host'     => env('DB_HOST', 'localhost'),
        'database' => env('DB_DATABASE', 'data'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'charset'  => 'utf8',
        'prefix'   => '',
        'schema'   => env('DB_SCHEMA', 'public'),
    ],

    'auth' => [
        'driver'   => 'pgsql',
        'host'     => env('DB_HOST', 'localhost'),
        'database' => env('DB_DATABASE_AUTH', 'authentication'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'charset'  => 'utf8',
        'prefix'   => '',
        'schema'   => env('DB_SCHEMA', 'public'),
    ]

The Laravel query I'm trying to run:

    Files::where('sender_id',$userId)
  ->join('authentication.users as users','Policies.sender_id','=','users.id')
  ->select('*')
  ->get();

The table Files belong to database data.

But this gives me an error SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "authentication.users" does not exist

I know the connection to the authentication table works since I can do this

DB::connection('auth')->table('users')->whereIn('id', '123')->first();

I've tried replacing authentication with auth but to no avail.

What am I doing wrong? Thanks.

Upvotes: 0

Views: 864

Answers (1)

Senthil
Senthil

Reputation: 2246

Using query builder :

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

using Eloquent model option:

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

This might be a duplicate ask. Pls refer Join two MySQL tables in different databases on the same server with Laravel Eloquent

Upvotes: 1

Related Questions