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?