I have 3 tables:

1. subdistrict (in database B)
2. users (in database A)
3. admin_area (in database A).

and now, i want to insert or display data for user with area. below is the code i have made:

Model User

<?php

namespace App\Models;

use App\Models\Marketplace\Subdistrict;
use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\SoftDeletes;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;
use Illuminate\Support\Facades\DB;
use Laravel\Sanctum\HasApiTokens;
use Spatie\Permission\Traits\HasRoles;

class User extends Authenticatable
{
    use HasApiTokens, HasFactory, Notifiable, HasRoles, SoftDeletes;

    public function areas()
    {
        return $this->belongsToMany(Subdistrict::class, 'admin_area', 'user_id', 'subdistrict_id');
    }

}

Model Subdistrict

<?php

namespace App\Models\Marketplace;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Subdistrict extends Model
{
    use HasFactory;

    protected $connection = 'marketplace';
    
    protected $table = 'subdistrict';

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

admin_area table:

• user_id
• subdistrict_id

If I try to get the areas from user_id = 1, I get an error like below

How to fix it? or How to make pivot table with different database in laravel 8?

CodePudding user response：

Barring any design considerations, you can try by including the database name in front of the table. I'm assuming that the databases are in the same server. Try alternating database_a or database_b (I'm not 100% clear on how the relationship was created).

class User extends Authenticatable
{
    use HasApiTokens, HasFactory, Notifiable, HasRoles, SoftDeletes;

    public function areas()
    {
        return $this->belongsToMany(Subdistrict::class, 'database_a.admin_area', 'user_id', 'subdistrict_id');
    }

}