CODEWITHSUNDEEP
phpmysqllaravellaravel-5
Frederick Zhang
Frederick Zhang

Reputation: 3683

Laravel 5.3 - How to perform a query across multiple databases?

We currently have multiple databases physically located in different places. The structures of them are exactly identical. We do this instead of partitioning due to some legal regulations (and for most of the times, we do queries to only one of them).

But sometimes we need to do the cross-database stuff and we could know which databases to query based on the request. I know this could be done with a loop in each of the APIs but that would be rather ugly and I want a programmatic way (we've got a number of APIs which could potentially get a cross-database query).

For example, could there be something like:

$batchModel = new Batch();
// an array instead of a string here
$batchModel->setConnection(['region1', 'region2']);
$batchModel->all();

Upvotes: 4

Views: 1319

Answers (2)

Melvin Koopmans
Melvin Koopmans

Reputation: 3050

Interesting.. As far as I know Laravel doesn't natively support this.

I can think of a way to make this work:

You can create multiple databases in your .env file:

DB_CONNECTION=region1
DB_HOST=adomain.com
DB_PORT=3306
DB_DATABASE=homestead
DB_USERNAME=homestead
DB_PASSWORD=secret

And another one:

DB_CONNECTION=region2
DB_HOST=anotherdomain.com
DB_PORT=3306
DB_DATABASE=homestead
DB_USERNAME=homestead
DB_PASSWORD=secret

Then add it to your database config:

'region1' => [
    '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', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
],

Do the same for region2.

Now extend the Eloquent class and add a method like "setMultipleConnections":

public method setMultipleConnections($databases, Model $model) {

   $collection = new Illuminate\Database\Eloquent\Collection;
   // Loop over $databases array and for each one of them get the collection. 
   foreach ($databases as $database) {
       $model = new $model;
       $model->setConnection($database);
       $model->get();

       $collections->merge($model);
   }

   return $collection;

}

Then I think you can call methods like all and find on that collection.

None of this works, it's just a quick draft. I think you know what I'm trying to achieve here. Is this something that you're looking for?

Upvotes: 1

dparoli
dparoli

Reputation: 9161

If you select the database based on the request I would go for a middleware i.e:

public function handle($request, Closure $next)
{        
    if( $request->has('something') ) {
        config(['database.default' => $request->something]);
    }
    return $next($request);
}

Upvotes: 0

Related Questions