laravel BelongsTo relationship with different databases not working

Question

I've seen in several places to "stay away" from this, but alas - this is how my DB is built:

class Album extends Eloquent {

   // default connection

   public function genre() {
       return $this->belongsTo('genre');
   }

and the Genre table:

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 doesn't select properly (doesn't add the database name to the table "genres"):

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

Its important to note that this works perfectly:

Album::first()->genre;

Update

The best I've found so far is to use the builder's "from" method to specifically name the correct connection. I've discovered that the builder inside the query can receive "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 in the database php and find a good way to get the proper table and database name from the relation 'genre'.

I will appreciate if anyone else can build on this solution and make it more general.

Answer 1

Solution for laravel v5.7 and above

class Album extends Eloquent {

   // default connection

   public function genre() {
       return $this->setConnection('Resources')->belongsTo('genre');
   }
...
}