Laravelで別々のDBのテーブルをjoinする方法

Question

前提・実現したいこと

お世話になっております！
題名のとおりですが、Laravelで別々のDBのテーブルをクリビルダでjoinする方法があれば教えていただきたいです！よろしくお願い致します！

# config/database.php

'connections' => [
....
         'mysql_1' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'hoge'),
            'username' => env('DB_USERNAME', 'hoge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],

        'mysql_2' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST_OTHER', '127.0.0.1'),
            'port' => env('DB_PORT_OTHER', '3306'),
            'database' => env('DB_DATABASE_OTHER', 'hoge'),
            'username' => env('DB_USERNAME_OTHER', 'hoge'),
            'password' => env('DB_PASSWORD_OTHER', ''),
            'unix_socket' => env('DB_SOCKET_OTHER', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],
....

【mysql_1/users】

user_id	name
1	てすと１
2	てすと２

【mysql_2/favorites】
|favorite_id|user_id|name|
|:--:|:--:|]--:|
|1|1|映画|
|2|2|読書|

※テーブル構造はとりあえずこのままで

試したこと

DB::table(DB::raw('mysql_1.users AS user'))
   ->join(DB::raw('mysql_2.favorites AS favorite'),'user.user_id','=','favorite.user_id')

補足情報（FW/ツールのバージョンなど）

Laravel 8.7
php 7.4

追加

DB::select(
    DB::raw("
            select x.user_id, x.name, y.name as favorite
            from mysql_1.users as x
            left join mysql_2.favorites as y on x.user_id = y.user_id
           ")
   );

Comments

[m.ts10806]

Laravelじゃなかったらできるんでしょうか。直接SQL書いたりとか。

[yoto009]

生のsqlだとできるみたいです。
追加しましたがLaravelでも一応rawを使って書けるようですが毎回rawを使いたくはないです…

Answer 1

以下の書き方でどうでしょうか？

DB::connection('mysql_1')
    ->table('users as user')
    ->join('mysql_2.favorites as favorite', 'user.user_id', '=', 'favorite.user_id');

解説

• Laravel は複数データベースを扱う際， PDO コネクションを別管理する設計思想
• そのため， DB ファサードの実体，すなわち Connection インスタンス（PDO のラッパー）を取得する段階で参照先 DB を切り替え， JOIN 対象だけデータベース名まで完全修飾する構文を使う

Eloquent への応用

class User extends Model
{
    protected $connection = 'mysql_1';

    public function hasMany(): HasMany
    {
        return $this->hasMany(Favorite::class);
    }
}

class Favorite extends Model
{
    protected $primaryKey = 'favorite_id';
    protected $connection = 'mysql_2';

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

Eloquent を使う場合，上記のように $connection プロパティの設定で透過的に対処できます。ラクなので個人的にはこれが一番好みです。

Comments

[yoto009]

> mpyw様
ご回答ありがとうございます！ご提示頂いた方法で取得できました！

ご丁寧に解説まで載せて頂きありがとうございます！ただ自分に理解力がなくていまいちピンと来なかったのでお聞きしたいのですが、connectinでベースのDBを指定して切り替え、別DBのテーブルjoinする場合は、何故connectionしなくてもDB名(database.phpのキー名がmysqlだったとしても)からテーブル名までを指定すろことで取得できるのでしょうか？

[mpyw]

「Laravel が FROM 句でのエイリアシングを考慮していない」かな？と思って書いてみたんですが，ローカル環境で SQL 生成を試してみたら普通に正しい結果になっているように見えますね。これでしたら，どっちのコネクションを使ったとしても動くように見えてしまいますね…

「できない」と判断したときのエラーなどを教えていただけませんか？

[yoto009]

変な質問をしてしまってすみません。できないと判断したわけではないので特にエラーがでたとかではないです。connectionした後にjoinではdb名からの指定で参照できるというのが？だったという感じですね。

[mpyw]

なるほど，でしたら元のコードでも問題はないように思います！但し， config/database.php で複数コネクションを定義する場合は回答のコードのように PDO 接続ごと切り替えて，SQL文中では「デフォルトのデータベース名」は省略するほうが自然なコードになるかな？と思いました

mysql_1 が Laravel 全体でデフォルト接続に設定されていてかつそこのテーブルを起点にする場合は，::connection() の部分は省略していきなり ::table() から入って大丈夫です。デフォルトではない mysql_2 を起点にする場合のみ ::connection('mysql_2') から入る必要があります。

[mpyw]

DB::table('mysql_1.users AS user')->join('mysql_2.favorites AS favorite', 'user.user_id', '=', 'favorite.user_id')

↑多分 DB::raw() なしでいけるんじゃないでしょうか？ "... as ..." という書き方は Laravel がパースして適切に取り扱ってくれるはずですね

[mpyw]

>> connectionした後にjoinではdb名からの指定で参照できるというのが

これに関しては，SQL レベルでそういう文法が定義されているから…が理由の1つと言えるでしょう。同じ MySQL システム上に複数のデータベースが存在している場合に限り，異なるデータベースを参照することができます。

[yoto009]

なるほど！勉強になります！細かい解説ありがとうございます！
この度はこちらの知識不足でお手を煩わせてしまい申し訳ありません！

[mpyw]

MySQL :: MySQL8.0リファレンスマニュアル:: 9.2.2識別子修飾子 https://dev.mysql.com/doc/refman/8.0/en/identifier-qualifiers.html?ff=nopfpls