Framework: whereHas is not working with several database

[givisok]

I'm getting error when try to use whereHas with two database.

class Tree extends Model 
{
        protected $connection = 'one';

        public function profile()
        {
           return $this->hasOne(profile::class, 'user_id', 'ancestor_id');
         }
}

class Profile extends Model
{
    protected $connection = 'two';
}

return Tree::where('ancestor_id', $id)->with('profile')
            ->whereHas('profile')->toSql();

I'v got
"select * fromtreewhereancestor_id= ? and exists (select * fromprofilewheretree.ancestor_id=profile.user_id)"

I try to debug builder and found that in Illuminate\Database\Query\Builder
we have

 /**
     * Set the table which the query is targeting.
     *
     * @param  string  $table
     * @return $this
     */
    public function from($table)
    {
        $this->from = $table;
        return $this;
    }

[rSammy]

24377

[themsaid]

This is currently not supported in laravel https://github.com/laravel/framework/issues/4649

[gutitrombotto]

Hi! I 've solved this by adding 'connection' and 'table' variables models specificing the database in wich the model is saved.

For example, i have a model called 'User' in the database called 'core_database' in the table users.
In the other side, i have a model called 'UserEvents' in the database called 'logs_database' in the table 'user_events'

So i will have a two conections on config/database.php file:

'core_db_connection' => [
        'driver' => 'mysql',
        'host' => host_ip,
        'port' => port,
        'database' => 'core_database',
        'username' => username,
        'password' => password,
        ....
    ],

'logs_db_connection' => [
        'driver' => 'mysql',
        'host' => host_ip,
        'port' => port,
        'database' => 'logs_database',
        'username' => username,
        'password' => password,
        ....
    ],

And the models will be like:

class User extends Authenticatable {
  protected $table = 'core_database.users';
  protected $connection = 'core_db_connection';
  ...
}

class UserEvents extends Model {
  protected $table = 'logs_database.user_events';
  protected $connection = 'logs_db_connection';
  ...
}

As @rs-sliske said, this was tested in databases in the same database server.
Database connections have the same host ip.
I have not tried with a different way

Using this configuration, i can make any query across two or more separeted database.
I hope this help you!

[Rattone]

@gutitrombotto are you sure this works? I've made same changes and this will not allow whereHas to function correctly..

[gutitrombotto]

Hi @Rattone

This works for me.
What type of relationship do you want to have?

In many to many relationships, i set the pivot table with the database before. For example:

public function related_models() {
        return $this->belongsToMany('App\Model',
            'database_name.pivot_table_name',
            'fk_id',
            'fk2_id')
            ->using('App\Models\PivotModel')
            ->withPivot([
                'id',
            ]);
    }

Maybe you can copy the code and i can tell you if it is the same use case as my.

[rs-sliske]

this only works when the 2 databases are on the same database server, and the credentials used for the primary connection also have access to the database/tables used by the relationship

[gutitrombotto]

You are right @rs-sliske.
Databases must be on the same database server.
In my case, all my databases are on the same RDS instance.
Database connections should have the same host ip. I have not tried with a different way

[Rattone]

this only works when the 2 databases are on the same database server, and the credentials used for the primary connection also have access to the database/tables used by the relationship

Thanks, my databases are on different servers :-(

Is there a way to have the same many to many relationship without joins? Something like lazy loading?