How to join Multiple databases from different hosts in Laravel

Question

Is there any way to use join query with Multiple databases from different servers?

my database.php is

'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'db1'),
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            ...
        ],

        'mysql2' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST_2', '192.233.****.*'),
            'port' => env('DB_PORT_2', '3306'),
            'database' => env('DB_DATABASE_2', 'db2'),
            'username' => env('DB_USERNAME_2', 'root'),
            'password' => env('DB_PASSWORD_2', ''),
         ...
        ]

and i need to implement it in join query.

My controller function is

public function function1(){
$db1 = DB::connection('mysql2');
$result = TABLE1::join($db1 . '.' . 'table2', 'table2.id','=','table1.table2_id');

return Datatables::of($result);

}

possible duplication. https://stackoverflow.com/questions/31847054/how-to-use-multiple-databases-in-laravel — chaos505, Dec 08 '21 at 12:24
Its differ from that, In mentioned question, they are using the same host. — Sowndhar Kumar R, Dec 08 '21 at 12:30
same logic applies. https://fideloper.com/laravel-multiple-database-connections — chaos505, Dec 08 '21 at 12:33
Yeah i have tried those logic. But cant able to use in Join. — Sowndhar Kumar R, Dec 08 '21 at 12:42
Does this answer your question? [Join tables from two different server](https://stackoverflow.com/questions/11114197/join-tables-from-two-different-server) — N69S, Dec 08 '21 at 13:17

N69S · Answer 1 · 2021-12-08T13:16:54.457

Short answer: You cant paginate them correctly.

To be able to use server side pagination, the data need to be from the same source and implement pagination.

The only solution you might have to be able to show a dataTable of the results with proper pagination is to get all the data (order them with the collection) and paginate in the JS using datatable.

Another solution would be to have two table with conventional server side pagination.

The most complicated solution and the best one (without using replication) is to use Federated Storage engine where it is like having a table that represents the one in the second database. That way you communicate with only one database and it handles the second one by itself.

How to join Multiple databases from different hosts in Laravel

1 Answers1