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

7
votes

I have two tables in two different databases. Both databases are hosted on same AWS RDS server. I have one user account which can access both databases. I defined two different connections in config\database.php:

return array(
    'default' => 'mysql',
    'connections' => array(
        # Our primary database connection
        'mysql' => array(
            'driver'    => 'mysql',
            'host'      => 'samehost',
            'database'  => 'database1',
            'username'  => 'user1',
            'password'  => 'pass1'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),
        # Our secondary database connection
        'mysql2' => array(
            'driver'    => 'mysql',
            'host'      => 'samehost',
            'database'  => 'database2',
            'username'  => 'user2',
            'password'  => 'pass2'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),
    ),
);

I have two models for table1 with a connection to database1 and table2 with a connection to database2. Both tables have a column id. How to join queries with Eloquent models for the rows with the same id?

5
phpmysqldatabaselaravel
just use databaseName.tableName it will work – krishn Patel
fideloper.com/laravel-multiple-database-connections – Fazal Rasel
@FazalRasel Thanks but it doesn't talk about how to join the tables. – Iman Sedighi
If you already defined your Model with different database like ` protected $connection = 'mysql2';`, then just Define normal relation between Models. – Fazal Rasel
@FazalRasel It didn't work. even I defined a relationship in both models it shows Call to undefined method Illuminate\Database\Query\Builder::table2 error. If I use ->with('table2') then I don't get the error but the result from table 2 is null – Iman Sedighi

5 Answers

14
votes

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();
4
votes

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.

2
votes

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.

1
votes

Some answers are correct so far, I just add this in case anyone is looking for complete dynamic solution (just make sure your databases are all in the same server & your account have access to them) (for this answer I assume you already setup your config file & model files)

$databaseName1 = (new Model1())->getConnection()->getDatabaseName();
$tableName1 = (new Model1())->getTable();
$tableName2 = (new Model2())->getTable();

then you can do whatever join methods you want with what Laravel provided, another mistake I see a lot of people make is they insisted on using the DB facade to start the query, that's not the case

$databaseName2 = (new Model2())->getConnection()->getDatabaseName();
DB::join($databaseName1 . '.' . $tableName1, function($join) use ($databaseName1, $tableName1, $databaseName2, $tableName2) {
    $join->on($databaseName1 . '.' . $tableName1 . '.id', $databaseName2 . '.' . $tableName2 . '.table_id');
})->...

works the same with

Model2::join($databaseName1 . '.' . $tableName1, function($join) use ($databaseName1, $tableName1, $tableName2) {
    $join->on($databaseName1 . '.' . $tableName1 . '.id', $tableName2 . '.table_id');
})->...

For the second method you do not need the second connection because Laravel will choose the connection based on your model's connection as default, same goes to data in all other related clauses (e.g where(), groupBy()). I usually name the variables according to their model names.

0
votes

A simple eloquent way to connect two models of different databases

class User extends Model {

  public function Company()
  {
    return $this->hasOne(Company::class);
  }
}

class Company extends Model {
  protected $connection = 'mysql2';

  public function User()
  {
    return $this->belongsTo(User::class);
  }
}