Categories: LaravelLaravel 6Laravel Eloquent ORMWeb应用开发Web框架

在 Laravel 6 Eloquent 中跨数据库 join 查询的实现

图1

1、表 table_a 在 A 数据库，表 table_b 在 B 数据库。两张表通过列 table_a.id 、table_b.table_a_id 关联在一起。

2、参考高级 Join 语句：https://learnku.com/docs/laravel/6.x/queries/5171#211e4f 。参考：https://stackoverflow.com/questions/41423603/join-two-mysql-tables-in-different-databases-on-the-same-server-with-laravel-elo 。如图1

图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、最终实现的代码如下

$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、最终生成的 SQL 如下，符合预期。如图2

图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

永夜