How do I join queries between multiple databases that are on another server with Laravel Eloquent?

Question

This is how we handle multiple DB connections with Laravel, which is not the Python PHP Framework (for whom it thinks is a duplicate of the message)

<?php return array( 'default' => 'mysql', 'connections' => array( # Our primary database connection 'mysql' => array( 'driver' => 'mysql', 'host' => 'host1', 'database' => 'database1', 'username' => 'user1', 'password' => 'pass1' 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', 'prefix' => '', ), # Our secondary database connection 'mysql2' => array( 'driver' => 'mysql', 'host' => 'host2', 'database' => 'database2', 'username' => 'user2', 'password' => 'pass2' 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', 'prefix' => '', ), ), ); 

And this is how you connect to this database.

 $user1 = User::on('mysql1')->where(/* ... */)->get() $user2 = User::on('mysql2')->where(/* ... */)->get() 

These are only SELECT queries. Therefore, Eloquent works flawlessly.

However, when I want to perform a JOIN query operation between these two databases, this seems impossible.

Answer 1

Basically, you cannot do this using the Eloquent ORM or the Laravel query constructor.

However, you can try using a raw expression ( https://github.com/laravel/framework/blob/8865d2276be94f0a3497ba3bd26c906ae9e91e1c/src/Illuminate/Database/Query/Expression.php#L13 ) or smth like this, but you have to write code to create a model from the query result.

If you are using laravel 4, this question may help: Laravel 4: how to run raw SQL?

Answer 2

Write the original query and use the laravels database methods to exit the data.

 $results = DB::select( DB::raw("SELECT Users.id, Properties.name FROM [Database1].[dbo].[Users] join [Database2].[dbo].[Properties] on Users.id = Properties.user_id where [so].[dbo].[Users].id = :user_id"), array('user_id' => Input::get('user_id', -1))); 

Answer 3

If these two databases are on different servers, you cannot do this. At least not easy. I read about the storage engine . But I have never done this before.

BUT

If the databases are on the same server, you can do this with a simple connection if the user has the correct permission !