Riotsmurf
1 year ago

How to properly use cross database relationships?

Posted 1 year ago by Riotsmurf

Hello, I can't seem to figure out how to have a table in one database have a relationship with another table in a different database.

like Customer uses the 'directory' database.

namespace App\Models;

use App\User;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Customer extends Model
{
    use SoftDeletes;

    protected $connection = 'directory';
    public $fillable = [
        'id',
        'customer_type_id',
        'user_id'
    ];

    public function owners(){
        return $this->hasMany(User::class)->whereHas('roles', function($query){ $query->where('name', 'owner');});
    }
   public function employees(){
        return $this->hasMany(User::class)->whereHas('roles', function($query){ $query->where('name', 'employee');});
    }
}

Then in the User::class just uses the default 'mysql' database

namespace App;

use App\Models\Customer_form;
use Laravel\Passport\HasApiTokens;
use Illuminate\Notifications\Notifiable;
use Illuminate\Database\Eloquent\SoftDeletes;
use Illuminate\Foundation\Auth\User as Authenticatable;

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

    protected $connection = 'mysql';
    protected $fillable = [
        'name', 'email', 'password',
    ];

    protected $hidden = [
        'password', 'remember_token',
    ];

    protected static function boot() {

        // create an event to happen on deleting
        static::deleting(function($user)  {
            $user->roles()->detach();
        });
    }

    public function roles()
    {
        return $this
            ->belongsToMany("App\Role")
            ->withPivot('id','can_read', 'can_create', 'can_update', 'can_delete');
    }

    public function customer(){

        return $this->belongsTo(App\Models\Customer::class, "customer_id", "id")->with("primaryName");
    }
}

Then i am calling this relationship in my controller like so:

$customerUsers = Customer::has('owners')->with('owners')->with('employees')->get();

Then it gives me an error like its trying to query just one of the databases looking for a table that doesn't exist in THAT database( "directory" )

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'directory.role_user' doesn't exist (SQL: select * from `customers` where exists (select * from `users` where `customers`.`id` = `users`.`customer_id` and exists (select * from `roles` inner join `role_user` on `roles`.`id` = `role_user`.`role_id` where `users`.`id` = `role_user`.`user_id` and `name` = owner)) and `customers`.`deleted_at` is null) ◀

Any idea what i am missing? Or can you not do cross-database relations?

Please sign in or create an account to participate in this conversation.