Laravel 5 using 2 db connections join 2 tables

Question

How can I join 2 tables which are located on 2 different servers.

I setup db configs:

'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => false,
            'engine' => null,
            'options'   => [
                \PDO::ATTR_EMULATE_PREPARES => true
            ]
        ],

        'mysql2' => [
            'driver' => 'mysql',
            'host' => '132.133.22.9',
            'port' => '3306',
            'database' => 'contracts',
            'username' => 'asdsdsa',
            'password' => 'asdsad',
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => false,
            'engine' => null,
            'options'   => [
                \PDO::ATTR_EMULATE_PREPARES => true
            ]
        ],

I'm able to access only one like DB::connection('mysql')->table('elements')->get();

How can I use leftJoin for two tables on two different servers?

lets say I have:

elements (server1): id, class_id

classes (server2) id, name

Possible duplicate of [Querying data by joining two tables in two database on different servers](https://stackoverflow.com/questions/5145637/querying-data-by-joining-two-tables-in-two-database-on-different-servers) — Josh, Jun 22 '18 at 07:27
@Josh the question is related to Laravel Eloquent or QueryBuilder — hxdef, Jun 22 '18 at 07:30
You can't use two connections in a single query builder object. Even without query builder you can't (out of the box) join tables from different database servers. — apokryfos, Jun 22 '18 at 08:07

score 1 · Answer 1 · answered Jun 22 '18 at 09:57

1

You can use

database one (test) include elements table and database two (test2) include classes table .

$result= DB::table('elements')
    ->leftjoin('test2.classes', 'elements.class_id', '=', 'classes.id')
    ->get();
dd($result);

you don't really need to specify first DB connection. It's by default.

answered Jun 22 '18 at 09:57

kishan kumar

11
1

my second database located on external server, thus its a must to use 2 connections. By the questions is whether its actually possible to achieve or no ? – hxdef Jun 22 '18 at 10:04

score 0 · Answer 2 · answered Jun 22 '18 at 07:54

0

test it :

for exammple i have two databases and one connection :

database one (test) include elements table and database two (test2) include classes table .

$result= DB::connection('mysql')->table('elements')
    ->join('test2.classes', 'elements.class_id', '=', 'classes.id')
    ->get();
dd($result);

answered Jun 22 '18 at 07:54

Alihossein shahabi

4,034
2
33
53

Yea that works fine with one connection, no issue, but how to do this with two different connections if its possible ? – hxdef Jun 22 '18 at 08:01
You should use ‍‍`Eloquent` and set `protected $connection = 'mysql2';` in Model – Alihossein shahabi Jun 22 '18 at 08:24

Laravel 5 using 2 db connections join 2 tables

2 Answers2