Join two MySQL tables in different databases on the same server with Laravel Eloquent

This solution worked for me:

Model1::where('postID',$postID)
      ->join('database2.table2 as db2','Model1.id','=','db2.id')
      ->select(['Model1.*','db2.firstName','db2.lastName'])
      ->orderBy('score','desc')
      ->get();

You can try in this way if you have both databases on the same connection and is set to default.

$query = DB::table('database1.table1 as dt1')->leftjoin('database2.table2 as dt2', 'dt2.ID', '=', 'dt1.ID');        
$output = $query->select(['dt1.*','dt2.*'])->get();

I have tried on my localhost its working.


It's tricky, but can be achieved. However there are some limitations, that may lead to raw solutions anyway.

Here's what you need, assuming db1 is default:

// class ModelOne
public function modelTwo()
{
return $this->hasOne('ModelTwo', 'id');
}

//class ModelTwo
protected $table = 'db2.model_two_table';

public function modelOne()
{
return $this->belongsTo('ModelOne', 'id');
}
// then
$model1 = ModelOne::with('modelTwo')->get();
$model1 = ModelOne::has('modelTwo')->first(); 
// and so on

Mind that you can't use prefix for you tables in the db config. Also, if you define non-default connections on one of the models, then you need to adjust $table for both.

You can also use different connections for each model and many features will work just like that, however you can't rely on the joins that Eloquent builds:

ModelOne::with('modelTwo')->get(); // works as expected - this is what you asked for
ModelOne::has('modelTwo')->get(); // error, no table found

of course unless you have the same schema, but then it's not what you wanted anyway.