在 Laravel 6 Eloquent 中跨数据库 join 查询的实现
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
1 2 3 4 5 6 7 8 | $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、最终实现的代码如下
1 2 3 4 5 6 7 8 9 10 11 | $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
1 2 3 4 5 6 7 8 | select count(*) as aggregatefrom `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 |
近期评论