Laravel BelongsTo relationships with different databases do not work

Question

I saw in several places to “stay away” from this, but, alas, this is how my database is built:

class Album extends Eloquent { // default connection public function genre() { return $this->belongsTo('genre'); } 

and table of genres:

 class Genre extends Eloquent { protected $connection = 'Resources'; } 

My database.php:

 'Resources' => array( 'driver' => 'mysql', 'host' => 'localhost', 'database' => 'resources', 'username' => 'user', 'password' => 'password', 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', 'prefix' => '', ), 'mysql' => array( 'driver' => 'mysql', 'host' => 'localhost', 'database' => 'my_data', 'username' => 'user', 'password' => 'password', 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', 'prefix' => '', ), 

and when I try to run

 Album::whereHas('genre', function ($q) { $q->where('genre', 'German HopScotch'); }); 

it is not selected properly (does not add the database name to the "genres" table):

 Next exception 'Illuminate\Database\QueryException' with message 'SQLSTATE[42S02]: Base table or view not found: 1146 Table 'my_data.genres' doesn't exist 

It is important to note that this works great:

 Album::first()->genre; 

Update

The best I've found so far is to use the from method to create a specific connection. I found that the builder inside the request can get "from"

 Album::whereHas('genre', function ($q) { $q->from('resources.genres')->where('genre', 'German HopScotch'); }); 

This is a decent solution, but it requires me to dig the php database and find a good way to get the correct table and database name from the relationship genre.

I would appreciate it if someone else could use this solution and make it more general.

Answer 1

This is my own decision, and it works for me in general, but it is very complicated.

I use the builder method from the method to correctly set the table and database inside the subquery. I just need to pass the correct information inside.

Suppose a subquery can be as complex as "genres.sample" or even deeper (which means that albums are related to genres and genres are related to samples) this is like

 $subQuery = 'genres.samples'; $goDeep = (with (new Album)); $tableBreakdown = preg_split('/\./', $subQuery); // = ['genres', 'samples'] // I recurse to find the innermost table $album->genres()->getRelated()->sample()->getRelated() foreach ($tableBreakdown as $table) $goDeep = $goDeep->$table()->getRelated(); // now I have the innermost, get table name and database name $alternativeConnection = Config::get("database.connections." . $goDeep->getConnectionName() . ".database"); // should be equal to the correct database name $tableName = $goDeep->getTable(); // I have to use the table name in the "from" method below Album::whereHas($subQuery, function ($q) use ($alternativeConnection, $tableName) { $q->from("$alternativeConnection.$tableName"); $q->where(....... yadda yadda); }); 

TL: other;

 Album::whereHas('genres', function ($q) { $q->from('resources.genres')->where(....); }); 

Answer 2

I found a really good article for myself: http://fideloper.com/laravel-multiple-database-connections

Basically you should specify your two connections in your configuration file as follows:

 <?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' => '', ), ), ); 

So your two connections are smoothed down to mysql and mysql2 .

You can then tell the eloquent that the "alias" is used like this:

 <?php class SomeModel extends Eloquent { protected $connection = 'mysql2'; } 

Then you can set up your relationship as usual.

tl; dr: Basically, instead of specifying the table name as $connection in eloquence, specify the connection alias in your configuration, and it should work.

Answer 3

Looks like Eager Loading will do what you want to do.

 Album::with(['genre' => function ($q) { $q->connection('Resources') ->where('genre', 'German HopScotch'); }]); 

Answer 4

Better to start the “Resources” in database.php with resources!

I am wondering if you can try?

 Album::whereHas('genre', function ($q) { $q->setConnection('resources')->where('genre', 'German HopScotch'); }); 

Answer 5

I had the same problem when the relationship did not work with connecting the model.

My solution was to override the belongsToMany method on the model trying to install. See the example below.

 <?php namespace App\Model; use Illuminate\Database\Eloquent\Model; use Illuminate\Database\Eloquent\Relations\BelongsToMany; class ConnectionModel extends Model { /** * Override method to allow inheriting connection of parent * * Define a many-to-many relationship. * * @param string $related * @param string $table * @param string $foreignKey * @param string $otherKey * @param string $relation * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany|BelongsToMany */ public function belongsToMany($related, $table = null, $foreignKey = null, $otherKey = null, $relation = null) { // If no relationship name was passed, we will pull backtraces to get the // name of the calling function. We will use that function name as the // title of this relation since that is a great convention to apply. if (is_null($relation)) { $relation = $this->getBelongsToManyCaller(); } // First, we'll need to determine the foreign key and "other key" for the // relationship. Once we have determined the keys we'll make the query // instances as well as the relationship instances we need for this. $foreignKey = $foreignKey ?: $this->getForeignKey(); $instance = new $related; // get connection from parent $instance->setConnection(parent::getConnectionName()); $otherKey = $otherKey ?: $instance->getForeignKey(); // If no table name was provided, we can guess it by concatenating the two // models using underscores in alphabetical order. The two model names // are transformed to snake case from their default CamelCase also. if (is_null($table)) { $table = $this->joiningTable($related); } // Now we're ready to create a new query builder for the related model and // the relationship instances for the relation. The relations will set // appropriate query constraint and entirely manages the hydrations. $query = $instance->newQuery(); return new BelongsToMany($query, $this, $table, $foreignKey, $otherKey, $relation); } } 

Answer 6

I had the same problem when the relationship did not work with the parent connection.

My solution was to override the belongsToMany method on the model trying to install. See the example below.

 <?php namespace App\Model; use Illuminate\Database\Eloquent\Model; use Illuminate\Database\Eloquent\Relations\BelongsToMany; class ConnectionModel extends Model { /** * Override method to allow inheriting connection of parent * * Define a many-to-many relationship. * * @param string $related * @param string $table * @param string $foreignKey * @param string $otherKey * @param string $relation * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany|BelongsToMany */ public function belongsToMany($related, $table = null, $foreignKey = null, $otherKey = null, $relation = null) { // If no relationship name was passed, we will pull backtraces to get the // name of the calling function. We will use that function name as the // title of this relation since that is a great convention to apply. if (is_null($relation)) { $relation = $this->getBelongsToManyCaller(); } // First, we'll need to determine the foreign key and "other key" for the // relationship. Once we have determined the keys we'll make the query // instances as well as the relationship instances we need for this. $foreignKey = $foreignKey ?: $this->getForeignKey(); $instance = new $related; $instance->setConnection(parent::getConnectionName()); $otherKey = $otherKey ?: $instance->getForeignKey(); // If no table name was provided, we can guess it by concatenating the two // models using underscores in alphabetical order. The two model names // are transformed to snake case from their default CamelCase also. if (is_null($table)) { $table = $this->joiningTable($related); } // Now we're ready to create a new query builder for the related model and // the relationship instances for the relation. The relations will set // appropriate query constraint and entirely manages the hydrations. $query = $instance->newQuery(); return new BelongsToMany($query, $this, $table, $foreignKey, $otherKey, $relation); } } 

Answer 7

Add a connection variable with a default connection in the genre model:

 protected $connection = 'mysql'; 

I had some relationship problems without adding this.