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

Question

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?

Auten

just use databaseName.tableName it will work – Sandarac

fideloper.com/laravel-multiple-database-connections – Thrombosis

@FazalRasel Thanks but it doesn't talk about how to join the tables. – Auten

If you already defined your Model with different database like ` protected $connection = 'mysql2';`, then just Define normal relation between Models. – Thrombosis

@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 – Auten

Answer 1

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

Auten

Is there a way to use the $connection in in the configuration for the table name? Database names might differ in diferrentent environments – Eighty

We should not add database name as hardcode. what if anyone change db name from env file – Delogu

what about a different server? – Tinatinamou

@TejasPatel: You can take database name from env file – Civilized

Answer 2

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.

Dislike

Thanks but you used query builder. I asked how to do it with Eloquent model. – Auten

I could make a join query for eloquent models from your answer but with some slight changes. Thanks – Auten

Answer 3

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.

Welt

This is what I'm looking for, but unfortunately I'm facing an error. My config files are properly defined. The models of the table from another database are working when called via a relationship in ->with(), but when using ->join() it throws an error SQLSTATE[42P01]: Undefined table: 7 ERROR: relation xxxx does not exist – Soph

I dun have your code, I guess the error is caused by wrong model used, when you use join and with clauses together, the very first model you used is the base, so whatever join you used, even though your data set have changed, the base model and its relationship defined in the base model is still the same set, when you join A and B, you can only used relationship of A. – Welt

This is was really helpful as I have 2 connection and for 2nd connection database name is dynamic. Thanks a lot! – Damek

Answer 4

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);
  }
}

Bonaventura

Answer 5

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.

Sofer

It works fine. As in my DB the real column name was not id I needed to add the name o the column as the third argument in both belongTo and hasOne. – Auten

Answer 6

Using a mish-mash of techniques above, I got the following to work for me using Laravel's relationship methods. I have the stock Laravel Users model in the base (default) database. I also have a DB connection on the same server called client which shards out each client database on it's own. At the top of the Client. models, I specify the connection:

protected $connection = 'client';

Then, with my properties model in the client database as well as the join table property_user, I can join Properties to User(s) or vice versa using Eloquent:

return $this->belongsToMany(
        'App\Models\User',
        $this->getConnection()->getDatabaseName().'.property_user',
        'property_id',
        'user_id'
    );

Hope this helps someone trying to do the same kinda thing.

Comical

I can confirm it works. – Earthiness