Implementation of Cross Database Join Query in Laravel 6 Eloquent
1. Table TABLE_A is in the A database, and the table TABLE_B is in the B database. The two tables are linked together via columns table_a.id , table_b.table_a_id .
2. Refer to the advanced JOIN statement:https://learnku.com/docs/laravel/6.x/queries/5171#211e4f. Reference:https://stackoverflow.com/questions/41423603/join-two-mysql-tables-in-different-databases-on-the-same-server-with-laravel-elo. as shown in Figure 1
Figure 1
$databaseName1 = (new Model1())->getConnection()->getDatabaseName();
$tableName1 = (new Model1())->getTable();
$tableName2 = (new Model2())->getTable();
$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');
});
3. The final code is as follows
$dba = (new A())->getConnection()->getDatabaseName();
$tablea = (new A())->getConnection()->getTablePrefix() . (new A())->getTable();
$dbb = (new B())->getConnection()->getDatabaseName();
$tableb = (new B())->getConnection()->getTablePrefix() . (new B())->getTable();
DB::table($dba . '.' . $tablea)
->leftJoin($dbb . '.' . $tableb, function ($join) use ($dba, $tablea, $dbb, $tableb) {
$join->on($dba . '.' . $tablea . '.id', $dbb . '.' . $tableb . '.table_a_id');
})
->whereIn($dbb . '.' . $tableb . '.role', ['unpublished', 'main'])
->whereNull($dba . '.' . $tablea . '.deleted_at')
->count();
4. The final generated SQL is as follows, which is in line with expectations. as shown in Figure 2
Figure 2
select
count(*) as aggregate
from
`dba`.`table_a`
left join `dbb`.`table_b` on `dba`.`table_a`.`id` = `dbb`.`table_b`.`table_a_id`
where
`dbb`.`table_b`.`role` in ('unpublished', 'main')
and `dba`.`table_a`.`deleted_at` is null
![在程序运行时报错：syntax error, unexpected ',', expecting ']'](https://www.shuijingwanwq.com/wp-content/uploads/2023/07/1-11-520x245.png)
