Framework: Access relational data of many to many relationship with multiple database connection with different server

[akkivaghasiya5]

• Laravel Version: 5.4.30
• PHP Version: 5.6.30

Description:

I'm working with multiple database connection on different servers. i.e, host1 and host2. My default database connection is host2. My project have two tables. users exists on host1 and tasks exists on host2.

There is a many to many relationship on both tables. Pivot table for this relationship is task_users which exists on host2.

My model files are here.

User.php

class User extends Authenticatable
{

    protected $connection = 'host1';

    public function tasks()
    {
        return $this->belongsToMany(Task::class, 'task_users', 'user_id', 'task_id');
    }
}

Task.php

class Task extends Model
{
    protected $connection = 'host2';

    public function users()
    {
        return $this->belongsToMany(User::class, 'task_users', 'task_id', 'user_id');
    }
}

With this model files, when I'm trying to get users of a task, I'm getting this error.

Base table or view not found: 1146 Table 'database_hosted_on_host1_server.task_users' doesn't exist.

Here is my Controller Code

$task = Task::find($taskId);
dd($task->users->toArray());

I have also tried this too. But it works only if both database are on same server.

I have also tried Defining Custom Intermediate Table Models from laravel documentation. But still getting error. I think I'm doing some mistake in pivot class.

Here is my code.

Task.php

class Task extend Model
{
    protected $connection = 'host2';

    public function users()
    {
        return $this->belongsToMany(User::class)->using(TaskUser::class);
    }
}

TaskUser.php

use IlluminateDatabaseEloquentRelationsPivot;

class TaskUser extends Pivot
{
    protected $connection = 'host2';
    protected $table = 'task_users';
}

With this code, when I'm trying to get users of a task, I'm getting this error.

Base table or view not found: 1146 Table 'database_hosted_on_host1_server.task_user' doesn't exist.

In both code, relational table is getting assigned with host1 connection (connection of related table i.e, users). But it exists in host2 connection. And also my default connection is host2.

I have almost spent too much time to resolve this. But didn't get any way. I don't know either this is issue in laravel or not supported in laravel or I'm doing in wrong way.

[Dylan-DPC]

Base table or view not found: 1146 Table 'database_hosted_on_host1_server.task_user' doesn't exist.

This clearly means that it is not catching the pivot you defined and still looking for a table with normal conventions. Without a pivot model, I don't think it will be possible to hunt for a pivot on another connection

[akkivaghasiya5]

@Dylan-DPC You are right. If I'm defining users function in Task.php as

return $this->belongsToMany(User::class, 'task_users')->using(TaskUser::class);

then I'm getting this error.

Base table or view not found: 1146 Table 'database_hosted_on_host1_server.task_users' doesn't exist.

So I'm not getting that why my npivot is not getting cached. I think I need to define newPivot method in TaskUser. But I'm not sure about it and if I'm right then how to define.

[themsaid]

It appears to me as a no-fix, many to many relationships on different connections would work in 1 direction only but not the other.

[prodigeris]

Is there any fix for this?

[rodrigopedra]

I don't think this belongs to the core as it is an odd case, but I needed to have this in a project and ended writing a custom relation and trait.

See this gist if it helps you:

https://gist.github.com/rodrigopedra/6d8b6c2e4be5e1cce66549fc079b3d26

[z-h-z-h]

@rodrigopedra good job!
please add $foreignPivotKey, $relatedPivotKey, $parentKey, $relatedKey as is done in belongsToMany

[renalpha]

@rodrigopedra
Boss!

[laurencei]

I don't think this belongs to the core as it is an odd case, but I needed to have this in a project and ended writing a custom relation and trait.

Closing as this is resolved by the custom trait above.

[abin12914]

Recently I have experienced this issue and the following code was used as the fix/workaround.
Task.php

class Task extend Model
{
    protected $connection = 'host2';

    public function users()
    {
        return $this->belongsToMany(User::class, (config('database.connections.host2.database'). '.task_users'), 'task_id', 'user_id');
    }
}

[hmrneves1]

Hello I had a similiar: https://stackoverflow.com/questions/64680691/laravel-many-to-many-different-databases/64681984

Since I doesn't have many expirience is this kind of relation, the workaround from @abin12914 is still applied at this days? It worked but I mean... it's an workaround...

Thanks.