Laravel Eloquent eager loading on a different connection

Question

I am trying to use Laravel Eloquent ORM to do a simple one to one relationship on two different connections.

Let say I do :

MyModel::on('secondary_connection')->get()

That is working fine.

When I do :

MyModel::on('secondary_connection')->with('AnotherModel')->get();

I am getting an error because eloquent is doing the AnotherModel SELECT statement on the default connection (instead of "secondary_connection").

I can't find a way to work this around.

My models are well defined since I can join them in my default connection.

Thoughts ?

I am not very familiar with this concept, but I would say no. Each model extends Eloquent class. — Pier-Yves Lessard, Feb 09 '15 at 20:05
Try `->with(['AnotherModel' => function($q){ $q->on('secondary_connection'); }])` — lukasgeiter, Feb 09 '15 at 21:35
@lukasgeiter `Builder` is created with the connection as dependency, and there's no way to change it on the already instantiated object. And that's what happens during eager loading, so no way to achieve it on the fly. — Jarek Tkaczyk, Feb 09 '15 at 23:50

Pier-Yves Lessard · Accepted Answer · 2020-08-14T23:11:39.317

Well, as I've been suggested by many users, it seems that there's no way to do this on the fly. My understanding of this is that Eloquent is incomplete when comes to manage multi-connection.

There's 2 way to work this around I could figure.

First, specify the connection in the model :

class MyModel {
    $protected connection = 'secondary_connection';
}

That is obviously a bad workaround since this model is only usable in one connection... but still works.

Then, as Jarek Tkaczyk suggested, it is possible to switch de default connection with the new one. But instead of doing it in the config file, it is possible to swap the PDO oject.

    $default = DB::getPdo(); // Default conn
    $secondary = DB::connection('secondary_connection')->getPdo();
    DB::setPdo($secondary);
    $result = MyModel::with('AnotherModel')->get();
    DB::setPdo($default);

That is a workaround that works and can be a clean solution. Next step is to put that switching mechanism in a nice Laravel way.

score 0 · Answer 2 · answered Feb 09 '15 at 23:48

0

There's literally no way to do it on the fly.

You need to alter the default connection in order to make Eloquent use it for the eager loaded models. You can wrap it in a helper method like this:

function on($connection, Closure $callback)
{
    // backup default connection
    $default = Config::get('database.default');

    // change for current query
    Config::set('database.default', $connection);

    // run the query
    $result = $callback();

    // restore the default connection
    Config::set('database.default', $default);

    return $result;
}

then just call like below:

$models = on('secondary_connection', function () {
   return MyModel::with('relation')->get();
});

answered Feb 09 '15 at 23:48

Jarek Tkaczyk

78,987
25
159
157

That didn't make it. Request of relation keep be sent to the wrong connection. If the connection is already open, changing the config file doesn't have an effect. – Pier-Yves Lessard Feb 10 '15 at 18:37
It works if you don't have `$connection` property set on the model. So, do you? – Jarek Tkaczyk Feb 10 '15 at 19:43
No I didn't. Have you made a previous query on your default connection before swapping the configuration? (which should instantiate a PDO). I am using Laravel 4.2 – Pier-Yves Lessard Feb 10 '15 at 21:16

score 0 · Answer 3 · answered Sep 18 '15 at 12:51

I ended up with replacing the simply relationship functions with a bit more custom onces. For instance my ->relatedModel is somewhat of a copy of the original in the Eloquent Model, but includes setting the connection:

public function relatedModel() {
  $instance = new \relatedModel;
  $instance->setConnection($this->getConnectionName());
  $query = $instance->newQuery();

  return new BelongsTo($query, $this, 'myForeignKey', $instance->getKeyName(), null);
}

I'm on Laravel 5, so maybe this is different to 4.2.

And since I was fiddling around with this issue a lot now here is a great way to get Validation done over another connection than the default: http://laravel.io/forum/10-29-2014-validation-rules-and-multiple-db-connections

If it is a model method, you can even shorten it down to:

$v = Validator::make($data, $rules);
$v->getPresenceVerifier()->setConnection($this->getConnectionName());

CrossProduct · Answer 4 · 2016-05-04T17:30:02.033

I also am dealing with this issue. I found a solution but it will only work if it is acceptable to configure the used connection through the .env file. This will not work if you want to change it dynamically although you could in that case use a bit of logic (call a function) to return the correct connection name.

The solution I have is to override the connectionName method of the Model class. In my case I have a base class for all my models that takes care of this:

abstract class ModelBase extends Model
{
    public function getConnectionName()
    {
        return env('ELOQUENT_DB_CONNECTION', parent::getConnectionName());
    }
}

As I mentioned you can put whatever logic you want in the getConnectionName so you can make it more dynamic.

score -1 · Answer 5 · answered Feb 28 '19 at 11:42

class MyModel {
    $protected connection = 'secondary_connection';
}

is the nice option as eloquent models are DB facades + extras. all DB facades functions also apply to eloquent models. therefore instead of changing connection in db facade we can change it here only there is no difference.

Laravel Eloquent eager loading on a different connection

5 Answers5