Laravelのクエリビルダーでテーブル結合(内部結合, 外部結合)する

Laravelのクエリビルダーを使用して、テーブルを内部結合や外部結合するときの方法について記載しています。

ほとんどSQLを書くのと変わらないくらいな記述量になるので、簡単な結合などはLaravelのモデルにhasOneやhasManyを設定して取得する方が良いかと思います。
Eloquentに付加できるリレーションについては、こちらで確認してみてください。

LaravelのEloquentでリレーションを作る方法！(hasOne, hasMany)

LaravelのEloqunetでリレーションを作る方法をまとめてみました。 Laravelのバージョン8で挙動を確認しています。 モデルから関連するモデルにリレーションを作成しておくと、簡単に関連先のデータを取得することが可能です。 複雑...

codelikes.com

2021.06.18

今回データベースはMysqlのサンプルデータとして公開されているこちらを使用しています。

内部結合(Join)する

joinメソッドを使用すると、内部結合できます。
fromを使うと別名をつけることが可能です。

SQLの内容は従業員のテーブルと、給与のテーブルをemp_no(従業員番号?)で結合しています。
そして、従業員名・誕生日と給与を昇順に3件だけ取得しています。

$results = Employee::select([
            'e.first_name',
            'e.last_name',
            'e.birth_date',
            's.salary',
          ])
          ->from('employees as e')
          ->join('salaries as s', function($join) {
              $join->on('s.emp_no', '=', 'e.emp_no');
          })
          ->orderBy('e.birth_date')
          ->limit(3)
          ->get();

joinメソッドの第1引数には、リレーション先のテーブル名を記載して別名をつけています。

第2引数には、クロージャで結合時の条件を記載します。
結合条件を複数書きたい場合は、$joinを並べて書いてあげると良いです。

get()の箇所をtoSql()にすると、文字列でSQLが返ってきますので、今回書いた処理でSQLを確認してみました。
SQLを出力すると、下記のようになります。

select
    `e`.`first_name`,
    `e`.`last_name`,
    `e`.`birth_date`,
    `s`.`salary`
from
    `employees` as `e`
    inner join
        `salaries` as `s`
    on  `s`.`emp_no` = `e`.`emp_no`
order by
    `e`.`birth_date` asc
limit 3;

しっかりとinner joinで結合されたSQLが発行されていることが確認できました。

getメソッドを呼ぶと書いた条件の内容で、モデルがコレクションに複数入った状態でデータが取得されます。
取得した内容は、下記のように使用することができます。

foreach ($results as $record) {
    echo "{$record->first_name}";
    echo "{$record->last_name}";
    echo "{$record->birth_date}";
    echo "{$record->salary}";
}

今回はEloquentのモデルから条件を書いて取得してみました。

下記のようにDBクラスのtableメソッドから呼ぶ方法もあります。
fromで書いていた箇所をtableに持っていくと良いです。同じようにSQLのas句で別名がつけれます。

\DB::table('employees as e')
  ->select([
    'e.first_name',
    'e.last_name',
    'e.birth_date',
    's.salary',
  ])
  // --- 省略 ---

今回取得した結果を出力してみると、こうなりました。

外部結合(Left Join, Right Join)する

joinの箇所をleftJoinに変えると、外部結合になります。

$results = Employee::select([
              'e.first_name',
              'e.last_name',
              'e.birth_date',
              's.salary',
            ])
            ->from('employees as e')
            ->leftJoin('salaries as s', function($join) {
                $join->on('s.emp_no', '=', 'e.emp_no');
            })
            ->orderBy('e.birth_date')
            ->limit(3)
            ->get();

SQLを出力すると、下記のように外部結合(left join)されていることが確認できました。

select
    `e`.`first_name`,
    `e`.`last_name`,
    `e`.`birth_date`,
    `s`.`salary`
from
    `employees` as `e`
    left join
        `salaries` as `s`
    on  `s`.`emp_no` = `e`.`emp_no`
order by
    `e`.`birth_date` asc
limit 3

right joinしたい場合は同じように、rightJoinメソッドを呼び出します。

->rightJoin('salaries as s', function($join) {
    $join->on('s.emp_no', '=', 'e.emp_no');
})

別名をつけるときには削除カラムに注意

今回はasを使って、別名をつけたテーブルを使用しています。
このときに、削除カラム(deleted_at)があるときには、下記のようにカラムがないというエラーになる場合があります。

SQLSTATE[42S22]: Column not found: 1054 Unknown column xxx.deleted_at

これはテーブルに別名をつけているため、カラムが見つからなくなってしまっています。(論理削除のときにLaravelで自動的にdeleted_atがnullか確認するため)
このときには、withTrashedメソッドをつけることで、削除カラムを参照せずに処理をすることが可能です。

Employee::select()
    ->from('employees as e')
    ->join('salaries as s', function($join) {
        $join->on('s.emp_no', '=', 'e.emp_no');
    })
    ->withTrashed()
    ->get();

論理削除されたカラムを含みたくないときには、whereNullメソッドで別名テーブルのdeleted_atを指定するようにしましょう。

おわりに

今回はクエリビルダーを使用して、テーブルを結合してデータを取得してみました。
ちょっと記述量も多くなり難しくなりますが、SQLが普通に書ける方は難なく使っていけるかと思います。

使いどころとしては、下記のような場合には使うと良いのではないでしょうか。
・Eloquentモデルに設定した簡単なリレーションでは実現が難しい。
・複雑なSQLを書きたい場合や複数のテーブルを結合したりする。