Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Riotsmurf's avatar

How to properly use cross database relationships?

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?

0 likes
16 replies
burlresearch's avatar

Typically when you are modelling relationships between data - you are sort of doing it implicitly within the same database.

Reaching across databases while trying to model a tightly-coupled relationship sort of breaks the notion ... what if the owner of the foreign dbase decides to change their schema? Then all of your relationships break - and there is nothing really preventing this.

Single databases can be made to span across multiple servers, and can be made to scale to great extent without requiring alternate dbase installations.

Perhaps you'd be better off accessing this kind of data through an API, rather than try to model some sort "relationship" - this seems really off, to me.

2 likes
bertholf's avatar

the answer is in: protected $connection = 'mysql';

set two connections up and then reference each in their appropriate model.

e.g.: /config/database.php

'connections' => [

    // First Database
    'database1' => [
        'driver' => ......
],

    // Second Database
    'database2' => [
    .....

Then in each model set the correct connection (database):

protected $connection = 'database1';

I have implemented this successfully, but caution that the whereHas does not seem to be working across databases

1 like
marxtinus's avatar

@bertholf Yes I confirm you can use with across DB but "has", "whereDoesntHave" don't seems to work. ->toSql show different approch for "with" and "has".

InspiredPrynce's avatar
public function your_method()
{
    $database = $this->getConnection()->getDatabaseName();

    return $this->belongsToMany('B', "$database.a_bs", 'a_id', 'b_id');
}
spar_x's avatar

Since I run into this issue often as I like to split my databases into multiple and use a lot of relationships between databases, the answer is quite simple. You have to prefix the database name (not the connection name, the actual database name) to the table definition on your models

So let' say you have a users table in a database called "laravel_core" and it has many comments stored in a comments table stored in a database called "laravel_data" then in your User.php model you add protected $table = 'laravel_core.users'; and in your Comment.php model you add protected $table = 'laravel_data.comments'; and voila, cross-database use of has and whereHas will now function properly

The problem arises when you are trying to do this on models from vendor packages where you cannot easily alter the model class

7 likes
Bhavil Jain's avatar

@spar_x

I know this is an old thread.

But what if I have multiple connections with different MySQL users and permissions

  • root MySQL user (with all privileges) as default connection
  • tenantXYZ MySQL user (with only CURD privileges on a single table) as tenant connection

ok, so I have 1 core database (myApp_core) and multiple tenant databases. Each tenant database has its own MySQL user & password (saved in myApp_core.companies table) The tenant user only has access to their own table i.e. table : tn_tenant_XXX

if I'm trying to apply your method, I get an error like

"message": "SQLSTATE[HY000] [1045] Access denied for user ''@'localhost' (using password: YES) (SQL: select * from `tn_shit1wotpz`.`company_addresses` where `tn_shit1wotpz`.`company_addresses`.`company_id` = 1 and `tn_shit1wotpz`.`company_addresses`.`company_id` is not null)",

The query is correct, but I can't understand why the user is '' ??

Any idea I could make things work??

Model 1 (Companies) : uses root connection and is trying to establish a hasMany relationship

Model 2 (Addresses) : uses tenant connection and is trying to establish a belongsTo relationship. (Though not necessary, as i will never reverse search a company form the address.)

As far as I can see, the root user shouldn't have permission issues with accessing the tenant table.

PS : all databases are on the same server.

matiaslauriti's avatar

@hvac_technologies This is not definitely the way... I was just going to state "what happens if you have a different user and password?".

You should have that for security reasons...

woutervanderhorst's avatar

I understand what you are doing in the model to define the tables from different DB.

But how do you make the pivot tables if a user has multiple comments?

abrada's avatar

Bad architecture design. Bad decision. From another database, you must receive data - in a separate request.

ryangurnick's avatar

@abrada There are generally lots of decisions that take place to build out application architecture, please try to provide helpful recommendations rather than being critical.

I am currently wondering the same thing where I am attempting to create a relationship between two tables that are in separate databases and the decision to put them in separate databases is actually crucial to the security and expandability of the application into the future.

MarkP's avatar

This is for those folks using multiple sqlite databases. The key is you have to "Attach" the other database.

public function districts()
{
    $database = $this->getConnection()->getDatabaseName();
    // it will error if the database is already attached
    $current_attachment = \Illuminate\Support\Facades\DB::connection('voter_files')->select("PRAGMA database_list");
    // test to see if it is already attached
    if (!in_array($database, array_column($current_attachment, 'file'))) {
        \Illuminate\Support\Facades\DB::connection('voter_files')->statement("ATTACH DATABASE '$database' AS pivot_db_main");
    }
    return $this->belongsToMany(District::class, 'pivot_db_main.campaign_district', 'campaign_id', 'district_link');
}
1 like
prog_24's avatar

I believe the way to resolve this is to specify the connection for each model in the file. This takes away the guess work.

See: github.com/mongodb/laravel-mongodb issues:2223

Please or to participate in this conversation.