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

Question

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 Illuminate\Database\Eloquent\Relations\Pivot;

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. If anyone knows the answer, it will be appreciated.

score 1 · Answer 1 · answered Aug 19 '17 at 15:18

1

On your model, try defining 'connection.table' like this:

User.php

class User extends Authenticatable
{

    protected $table = 'host1.users';

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

Task.php

class Task extend Model
{
    protected $table = 'host2.tasks';

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

And your pivot TaskUser.php

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

answered Aug 19 '17 at 15:18

Diego Blaker

341
1
7

I'm getting this error if I make changes as per your answer. `Base table or view not found: 1146 Table 'host2.tasks' doesn't exist` – Akshay Vaghasiya Aug 21 '17 at 03:49
Did you test your database connections? – Diego Blaker Aug 21 '17 at 14:14
I have tested my database connection. Both are connected. – Akshay Vaghasiya Aug 22 '17 at 06:02

score 0 · Accepted Answer · answered Aug 24 '17 at 11:45

0

Okay. I got the answer from themsaid on github. He said that many to many relationships on different connections would work in 1 direction only but not the other. Here you can find github issue.

answered Aug 24 '17 at 11:45

Akshay Vaghasiya

1,597
9
36
60

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

2 Answers2

Linked