Level 47
In the model you can use
protected $connection = 'your connection name';
Then you don't need to add it any where else.
Apr 17, 2016
13
Level 6
Use different connection for eager loading
I am using a different database connection (aka, not the default one) for a lot of my queries. I am able to dynamically set the connection when running an Eloquent query, but Eloquent's eager loading seems to use the default connection even though the parent query is using a different one.
I know that you can specify a connection for a model via its constructor
class SomeModel {
public function __construct() {
$this->connection = 'some-connection';
}
// . . .
}
However, the connection is dynamically specified with each query, so manually specifying it in the constructor won't work in my case.
How else can I get eager loaded models to use the same connection as their parent?
Level 6
I know that you can set the connection in the model itself. However, I'm using multiple connections, and they are dynamically specified with each query. So "manually" setting the connection in the model's class won't work in this case.
1 like
Level 47
Advance wheres then
Model::with(['some relation' => function($q) use('some connection') {
$q->connection('some connection');
}])->get();
Level 6
I've tried that as well. It comes back with
Call to undefined method Illuminate\Database\Query\Builder::connection()
Level 47
Probably have to set it on the relationship then.
Level 6
Hmm. How would you recommend going about that?
Level 54
something like this should work...
public function someRelationship() {
$this->connection = 'some alternate connection';
return $this->hasMany('Foo');
}
Level 1
@willvincent wouldn't it change connection for other loadings ? or maybe it would be within one object, so couldn't influence other loads
Level 17
Such advance topic... hope this will be resolve ill keep an eye on this thread...
Level 1
Hello! I know.. 6 years old topic, but it's the only on the internet describing exactly what I am looking for. Is there any solution to this? How can we force loaded relation use same connection as model it is called from? In my environment connection always falls back to default app connection.
Hardcoding $connection = 'something'; is not an option as it must be dynamic. Same models & relations are used to connect to different databases, but with the same db structure., only data within those databases is different.
Running on Laravel 5.8 btw.
Any help is greatly appreciated, Thanks.
1 like
Level 1
Ok I worked out how to dynamically change the database connection in a query, In my case I have to dynamically connect to 5+ different opencart databases to sync products and prices, so I have 4 different database connections I need to connect to at runtime.
This is what I did
#1 Don't set the connection in Models needs a dynamic connections It causes problems as Connections are Inherited in Eager Loading if there is no fixed connection, allow this to be propagated through the query, every ->with(''abc","abc.def","abc.def.xyz") "def" and "xyz" will inherit the parents connection
#2 If you have a relationship to the Laravel database from a model which uses the dynamic connections , set the connection to null and it will use the default Laravel connection, breaking the inheritance
return $this->setConnection(null)->BelongsTo(...
or
return $this->setConnection("LaravelDBConnection")->BelongsTo(...
#3 When you run a query on the dynamic model, set the connection before you query, it's not static so you need to create a new model first, note that this connection setting will propogate to all eager loading children
$connectionName = "OtherDatabaseConfig'
$ShopProduct = new ShopProduct;
$products = $ShopProduct->setConnection(connectionName)->where('status', 1)->with(["ShopOptions","ShopHistory","ShopPrices"])
#4 The problem will arrise, if one of the Eager Loaded models is a LaravelModel as the query gets reset back to Laravel Databse, and if you need access dynamic tables in the children it will fail saying it can not find the table, in this case you need to change the Query Connection manually
In the examples below, ShopXXX are Models to the dynamic Shop, LaravelXXX is a Model to the default Database
Ths will not work....
$products = $ShopProduct->setConnection($connectionName)->where('status', 1)->with([
"ShopOptions",
"ShopHistory",
"ShopPrices",
"LaravelCustomer" ,
"LaravelCustomer.ShopProducts" // << THIS WILL NOT WORK AS IHERITS LARAVEL CONECTION
"LaravelCustomer.ShopProducts.ShopOptions" // << THIS WILL NOT EITHER
])
To fix this we need to change the Query Connection
$connectionName = "OtherDatabaseConfig"
//We need to change the connection to an
// Illuminate\Database\Connection which can easily be done from
// resolveConnection on any Model which has been initialised
// Change the connectionName to a Connection object, we will use later on
$connectionInterface = $ShopProduct->resolveConnection($connectionName);
$products = $ShopProduct->setConnection($connectionName)->where('status', 1)->with([
"ShopOptions",
"ShopHistory",
"ShopPrices",
"LaravelCustomer" => function($query) use ($connectionInterface,$connectionName){
// The connection here is to Laravel Database
// Which is correct for "LaravelCustomer" but not the children "ShopProducts"
return $query->with([
"ShopProducts" => function($query) use ($connectionInterface, $connectionName) {
// The current connection here is inherited from "LaravelCustomer" and is wrong
// Here we need to change the connection to the SHOP connection
// To do that, we set it directly on the Base Connection using the object we created
$query->toBase()->connection = $connectionInterface;
// Remember when we said connections are inherited from the Model
// the Model also need to be updated so "ShopOptions" gets the correct connection
//You can skip setConnection() if you have no more eager loadng
// from "ShopProducts" .
//Change the connection name on the Model so child query's
// "ShopOptions" will inherit the Shop connection
$query->getModel()->setConnection($connectionName);
return $query->with("ShopOptions");
}])
])
in Short, To Dynamically change eager loading for each Model update the query with this below before you return the query.
function($query) use ($connectionName) {
$query->toBase()->connection = $query->getModel()->resolveConnection($connectionName);
return $query
}
With Children
function($query) use ($connectionName) {
$query->toBase()->connection = $query->getModel()->resolveConnection($connectionName);
$query->getModel()->setConnection($connectionName);
return $query->with("ModelThatUsesTheSameConnection")
}
UPDATE 16 Sept 2022
Your ->with() MUST be the last thing in the query
// THIS WORKS
$ShopProduct->setConnection($connectionName)->where('status', 1)->with( ...)->get()
// THIS FAILS
$ShopProduct->setConnection($connectionName)->with( ...)->where('status', 1)->get()
4 likes
Level 1
@Glenn this solution worked for me. Thanks
Level 5
Thanks for new tips
Please or to participate in this conversation.