$items = MainTable::select('main_table.id', 'main_table.title')
    ->with('details.category.type')
    ->join('sub_table', 'main_table.details_id', '=', 'sub_table.id')
    ->join('sub_2_table', 'sub_table.category_id', '=', 'sub_2_table.id')
    ->join('sub_3_table', 'sub_2_table.type_id', '=', 'sub_3_table.id')
    ->get()
    ->map(function ($item) {
        $title = $item->title;
        if ($item->details) {
            $title .= ' (' . $item->details->category->type->label . ')';
        }
        return [
            'id' => $item->id,
            'title' => $title,
        ];
    });

という業務で出くわしたクエリビルダから色々とSQLの深い知識を学ぶことができたので今回はそれをまとめようかと思います。

※テーブル名やカラム名は適宜命名を変えています。
※LaravelのコードですがLaravelを理解していなくても読める内容です。

学んだことその１

joinでは想定していない挙動にならないように、クエリビルダでも基本 SELECTが必須（JOINクエリにおけるカラム指定の重要性）

例えば、selectを指定した場合とそうでない場合で以下のようにデータが違ってきます。

Case1: selectを指定した場合

SELECT main_table.id, main_table.title
FROM main_table
    INNER JOIN sub_table ON main_table.details_id = sub_table.id
    INNER JOIN sub_2_table ON sub_table.category_id = sub_2_table.id
LIMIT 2;

結果

+-----+----------------+
| id  | title          |
+-----+----------------+
|  20 | ITEM-A001      |
| 155 | ITEM-A002      |
+-----+----------------+

Case2: selectを指定しなかった場合

SELECT *
FROM main_table
    INNER JOIN sub_table ON main_table.details_id = sub_table.id
    INNER JOIN sub_2_table ON sub_table.category_id = sub_2_table.id
LIMIT 2;

| id | details_id | title     | created_at           | updated_at           | deleted_at           | id | category_id | label          | created_at           | updated_at           | deleted_at | id | type_id | label          | created_at           | updated_at           | deleted_at |
|-----|------------|-----------|---------------------|---------------------|---------------------|----|-------------|----------------|---------------------|---------------------|------------|----|----------|----------------|---------------------|---------------------|------------|
| 20  | 1          | ITEM-A001 | 2022-08-18 13:14:40 | 2023-02-06 03:43:16 | 2022-09-02 20:48:43 | 1  | 1           | TYPE-A         | 2022-08-18 12:01:11 | 2023-05-25 15:25:41 | NULL       | 1  | 1        | TYPE-A         | 2022-08-18 12:01:10 | 2023-05-25 15:25:41 | NULL       |
| 155 | 1          | ITEM-A002 | 2022-08-18 13:14:48 | 2023-02-06 03:43:28 | 2022-09-02 20:49:13 | 1  | 1           | TYPE-A         | 2022-08-18 12:01:11 | 2023-05-25 15:25:41 | NULL       | 1  | 1        | TYPE-A         | 2022-08-18 12:01:10 | 2023-05-25 15:25:41 | NULL       |

注目すべき点

重複するカラム（main_table, sub_table, sub_2_tableの各テーブル）
- id: 3回出現
- title/label: 3回出現
- created_at, updated_at, deleted_at: 各テーブルに存在

になり、その結果たとえば、

 return [
    　'id' => $item->id, // 本来はmain_tableのＩＤを想定
     'title' => $title, // 本来はmain_tableのtitleを想定
 ];

の結果が本来はmain_tableのカラムを想定したとしても実際に表示されるデータが変わってしまうので注意が必要です

学んだことその２

EagerLoadingを指定しているので、SELECT 句に関連する外部キー（main_table.details_id）が必要

解説

->with('details.category.type')

のようにしていますが、

↓悪い例

MainTable::select('main_table.id', 'main_table.title')

のようにすると外部キーが含まれていなく、リレーション先のテーブルg呼ばれないので、
select文にmain_table.details_idを追加する必要がありました。

↓正解　(main_table.details_idを追加)

MainTable::select('main_table.id', 'main_table.title', main_table.details_id)

補足

ちなみにGolangのgormではselect文でmain_table.details_idのような指定をせずに下記のように記述できたりもします。便利ですね。

Gormの記述

    err := r.DB.
        InnerJoins("Details.Category.Type").
        Preload("Details.Category.Type").
        Find(&items).Error

実際のSQL

SELECT
    `main_table`.`id`,
    `main_table`.`created_at`,
    `main_table`.`updated_at`,
    `main_table`.`deleted_at`,
    `main_table`.`details_id`,
    `main_table`.`reference_id`,
    `main_table`.`title`,
    `main_table`.`department_id`,
    `main_table`.`is_active`,
    `main_table`.`status`,
    `main_table`.`code1`,
    `main_table`.`code2`,
    `main_table`.`code3`,
    `Details`.`id` AS `Details__id`,
    `Details`.`created_at` AS `Details__created_at`,
    `Details`.`updated_at` AS `Details__updated_at`,
    `Details`.`deleted_at` AS `Details__deleted_at`,
    `Details`.`category_id` AS `Details__category_id`,
    `Details`.`label` AS `Details__label`,
    `Details`.`variety` AS `Details__variety`,
    `Details`.`manufacturer` AS `Details__manufacturer`,
    `Details`.`max_capacity` AS `Details__max_capacity`,
    `Details`.`finish` AS `Details__finish`,
    `Details`.`structure` AS `Details__structure`,
    `Details`.`power_type` AS `Details__power_type`,
    `Details`.`production_date` AS `Details__production_date`,
    `Details`.`model_code` AS `Details__model_code`,
    `Details`.`material` AS `Details__material`,
    `Details`.`special_feature` AS `Details__special_feature`,
    `Details`.`note` AS `Details__note`,
    `Details__Category`.`id` AS `Details__Category__id`,
    `Details__Category`.`created_at` AS `Details__Category__created_at`,
    `Details__Category`.`updated_at` AS `Details__Category__updated_at`,
    `Details__Category`.`deleted_at` AS `Details__Category__deleted_at`,
    `Details__Category`.`type_id` AS `Details__Category__type_id`,
    `Details__Category`.`sort_order` AS `Details__Category__sort_order`,
    `Details__Category`.`label` AS `Details__Category__label`,
    `Details__Category`.`note` AS `Details__Category__note`,
    `Details__Category__Type`.`id` AS `Details__Category__Type__id`,
    `Details__Category__Type`.`created_at` AS `Details__Category__Type__created_at`,
    `Details__Category__Type`.`updated_at` AS `Details__Category__Type__updated_at`,
    `Details__Category__Type`.`deleted_at` AS `Details__Category__Type__deleted_at`,
    `Details__Category__Type`.`group_id` AS `Details__Category__Type__group_id`,
    `Details__Category__Type`.`org_code` AS `Details__Category__Type__org_code`,
    `Details__Category__Type`.`sort_order` AS `Details__Category__Type__sort_order`,
    `Details__Category__Type`.`label` AS `Details__Category__Type__label`
FROM
    `main_table`
    INNER JOIN
        `sub_table` `Details`
    ON  `main_table`.`details_id` = `Details`.`id`
    INNER JOIN
        `sub_2_table` `Details__Category`
    ON  `Details`.`category_id` = `Details__Category`.`id`
    INNER JOIN
        `sub_3_table` `Details__Category__Type`
    ON  `Details__Category`.`type_id` = `Details__Category__Type`.`id`

最後に

他にも色々学ぶところがありましたが、主にこの２つがかなり勉強になりました！

学んだこと その１