Laravelでsqlの特訓をする

:::note
以下の記述を比較できるようまとめました
- 素のsql
- Eloquentモデル
- クエリビルダ
:::

# 背景
SQLが苦手です。業務でLaravelを使っているのでLaravelを通して特訓したいと思います。

以下のリポジトリで特訓します。

https://github.com/yamatai12/laravel-sql

# テーブルのER図

```mermaid
erDiagram
    employees {
        INT id PK "従業員ID（主キー）"
        VARCHAR(50) name "名前"
        VARCHAR(50) position "役職"
        INT department_id FK "部門ID（外部キー）"
    }

    departments {
        INT id PK "部門ID（主キー）"
        VARCHAR(50) department_name "部門名"
    }

    projects {
        INT id PK "プロジェクトID（主キー）"
        VARCHAR(50) project_name "プロジェクト名"
        INT department_id FK "部門ID（外部キー）"
    }

    employee_project {
        INT employee_id FK "従業員ID（外部キー）"
        INT project_id FK "プロジェクトID（外部キー）"
    }

    employees ||--o{ departments : "belongs to"
    departments ||--o{ projects : "has"
    employees ||--o{ employee_project : "participates in"
    projects ||--o{ employee_project : "includes"

```

# テーブル
## employees テーブル

| id | name    | position   | department_id |
|-------------|---------|------------|---------------|
| 1           | Alice   | Manager    | 1             |
| 2           | Bob     | Developer  | 1             |
| 3           | Charlie | Developer  | 2             |
| 4           | David   | Analyst    | 2             |

---

## departments テーブル

| id | department_name |
|---------------|------------------|
| 1             | Engineering      |
| 2             | Research         |

---

## projects テーブル

| id | project_name | department_id |
|------------|--------------|---------------|
| 1          | Project A    | 1             |
| 2          | Project B    | 1             |
| 3          | Project C    | 2             |

---

## employee_project テーブル

| employee_id | project_id |
|--------------|------------|
| 1            | 1          |
| 2            | 1          |
| 2            | 2          |
| 3            | 3          |
| 4            | 3          |

# Eloquentモデルでリレーションを定義する

## Employeeモデル

Employeeモデルを作成します。
```terminal
sail artisan make:model Employee
```

```sample-app/app/Models/Employee.php
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use App\Models\Department;
use App\Models\Project;

class Employee extends Model
{
    // employeesテーブルは、departmentsテーブルと多対1の関係
    public function department()
    {
        return $this->belongsTo(Department::class);
    }

    // employeesテーブルは、projectsテーブルと多対多の関係
    public function projects()
    {
        return $this->belongsToMany(Project::class, 'employee_project');
    }
}

```

## Departmentモデル

Departmentモデルを作成します。

```terminal
sail artisan make:model Department
```

```sample-app/app/Models/Department.php
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Department extends Model
{
    // departmentsテーブルは、employeesテーブルと1対多の関係
    public function employees()
    {
        return $this->hasMany(Employee::class);
    }

    // departmentテーブルは、projectsテーブルと1対多の関係
    public function projects()
    {
        return $this->hasMany(Project::class);
    }
}

```

## Projectモデル

Projectモデルを作成します。
```terminal
sail artisan make:model Project
```

```sample-app/app/Models/Project.php
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Project extends Model
{

    // projectsテーブルは、departmentsテーブルと多対1の関係
    public function department()
    {
        return $this->belongsTo(Department::class);
    }

    // projectsテーブルは、employeesテーブルと多対多の関係
    public function employees()
    {
        return $this->belongsToMany(Employee::class, 'employee_project');
    }
}

```

# SQL練習問題

</details>

<details> <summary>問題1: 役職がManagerである従業員の数をカウントする</summary>

```sql
SELECT COUNT(*) FROM employees WHERE position = 'Manager';
```

```sample-app/app/Http/Controllers/SqlTrainingController.php
//Eloquentモデル
$count = Employee::where('position', 'Manager')->count();
```

```sample-app/app/Http/Controllers/SqlTrainingController.php
//クエリビルダ
$count = DB::table('employees')->where('position','Manager')->count();
```

</details>

<details> <summary>問題2: 従業員の名前と所属する部門の名前を一緒に取得する</summary>

```sql
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
```

```sample-app/app/Http/Controllers/SqlTrainingController.php
//Eloquentモデル
$employees = Employee::with('department')->get()->map(function ($employee) {
            return [
                'name' => $employee->name,
                'department' => $employee->department->name,
            ];
        });
```

:::note
with メソッドを使用することで、関連するモデル（ここでは department）を事前に読み込むことができます。
これにより、N+1 問題を回避し、データベースへのクエリ数を減らすことができます。
:::

```sample-app/app/Http/Controllers/SqlTrainingController.php
//クエリビルダ
$employees = DB::table('employees')
            ->select('employees.name','departments.name as department_name')
            ->join('departments', 'employees.department_id', '=', 'departments.id')
            ->get();
```

</details>

<details>
<summary>問題3: Project Aに参加している従業員のリストを取得する</summary>

```sql
SELECT employees.*
FROM employees
JOIN employee_project ON employees.id = employee_project.employee_id
JOIN projects ON projects.id = employee_project.project_id
WHERE projects.project_name = 'Project A';
```

```sample-app/app/Http/Controllers/SqlTrainingController.php
//Eloquentモデル
$projectA = Project::where('name', 'Project A')->first();
$employees = $projectA->employees()->get();
```

```sample-app/app/Http/Controllers/SqlTrainingController.php
//クエリビルダ
$employees = DB::table('employees')
        ->join('employee_project', 'employees.id', '=', 'employee_project.employee_id')
        ->join('projects', 'employee_project.project_id', '=', 'projects.id')
        ->where('projects.name','Project A')
        ->select('employees.*')->get();
```

</details>

<details> <summary>問題4: 部門名ごとの従業員の数をカウントする</summary>

```sql
SELECT departments.name, COUNT(*) AS employee_count
FROM employees
JOIN departments ON employees.department_id = departments.id
GROUP BY departments.id;
```

```sample-app/app/Http/Controllers/SqlTrainingController.php
//Eloquentモデル
$departments = Department::withCount('employees')->get();
```
:::note
withCountメソッドは、1つのクエリでリレーションを持つモデル（引数で指定したモデル）のカウントを取得します。
各departmentインスタンスにemployees_countプロパティが追加されます。
これに各部門ごとの従業員数の値が入ります。

`$department`を$departmentの要素だとすると、
`$department->employees_count`でアクセスできます。
:::

```sample-app/app/Http/Controllers/SqlTrainingController.php
//クエリビルダ
$departmentCounts = DB::table('departments')
    ->join('employees', 'departments.id', '=', 'employees.department_id')
    ->select('departments.department_name', DB::raw('COUNT(*) as employee_count'))
    ->groupBy('departments.id', 'departments.department_name')
    ->get();
```

</details>

<details> <summary>問題5: プロジェクト Bに参加している全従業員の役職を取得する</summary>

```sql
SELECT employees.name,employees.position
FROM employees
JOIN employee_project ON employees.id = employee_project.employee_id
JOIN projects ON employee_project.project_id = projects.id
WHERE projects.project_name = 'Project B';
```

```sample-app/app/Http/Controllers/SqlTrainingController.php
//Eloquentモデル
$projectB = Project::where('name', 'Project B')->first();
$employees = $projectB->employees()->get()->map(function ($employee) {
        return [$employee->name, $employee->position];
});
```

```sample-app/app/Http/Controllers/SqlTrainingController.php
//クエリビルダ
$employeesInProjectB = DB::table('employees')
    ->join('employee_project', 'employees.id', '=', 'employee_project.employee_id')
    ->join('projects', 'employee_project.project_id', '=', 'projects.id')
    ->select('employees.name', 'employees.position')
    ->where('projects.project_name', 'Project B')
    ->get();
```

</details>

<details> <summary>問題6: 部門ごとのプロジェクトの数をカウントする</summary>

```sql
SELECT departments.name, COUNT(projects.id) AS project_count
FROM departments
LEFT JOIN projects ON departments.id = projects.department_id
GROUP BY departments.id;
```

```sample-app/app/Http/Controllers/SqlTrainingController.php
//Eloquentモデル
$department = Department::withCount('projects')->get()->map(function ($department) {
            return [
                'name' => $department->name,
                'projects_count' => $department->projects_count
            ];
        });
```

```sample-app/app/Http/Controllers/SqlTrainingController.php
//クエリビルダ
 $department = DB::table('departments')
            ->select('departments.name', DB::raw('COUNT(projects.id) as projects_count'))
            ->leftJoin('projects', 'departments.id', '=', 'projects.department_id')
            ->groupBy('departments.id')
            ->get();
```

</details>

<details> <summary>問題7: 従業員のBobが参加しているプロジェクトのリストを取得する</summary>

```sql
SELECT projects.project_name
FROM projects
JOIN employee_project ON projects.id = employee_project.project_id
JOIN employees ON employees.id = employee_project.employee_id
WHERE employees.name = 'Bob';
```

```sample-app/app/Http/Controllers/SqlTrainingController.php
//Eloquentモデル
$bobsProj = Employee::where('name', 'Bob')->first()->projects->map(function ($project) {
            return $project->name;
        });
```

```sample-app/app/Http/Controllers/SqlTrainingController.php
//クエリビルダ
$bobsProj = DB::table('employees')
            ->where('employees.name', 'Bob')
            ->join('employee_project', 'employees.id', '=', 'employee_project.employee_id')
            ->join('projects', 'employee_project.project_id', '=', 'projects.id')
            ->select('projects.name')
            ->get()->map(function ($project) {
                return $project->name;
            });
```

</details>

<details> <summary>問題8: 役職ごとの従業員のリストを取得する</summary>

```sql
SELECT position, GROUP_CONCAT(name) AS employees
FROM employees
GROUP BY position;
```

```sample-app/app/Http/Controllers/SqlTrainingController.php
//Eloquentモデル
$employeesPosition = Employee::all()->groupBy('position')->map(function ($employees, $position) {
            return
                $employees->pluck('name')->implode(', ')
            ;
        });
```

```sample-app/app/Http/Controllers/SqlTrainingController.php
//クエリビルダ
$employeesPosition = DB::table('employees')
            ->select('position', DB::raw('GROUP_CONCAT(name) AS employees'))
            ->groupBy('position')
            ->get();
```
</details>

<details> <summary>問題9: 部門ごとの従業員とプロジェクトの数を取得する</summary>

```sql
SELECT d.department_name, COUNT(e.id) AS employee_count, COUNT(p.id) AS project_count
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
LEFT JOIN projects p ON d.id = p.department_id
GROUP BY d.id;
```

```sample-app/app/Http/Controllers/SqlTrainingController.php
//Eloquentモデル
$department = Department::withCount('employees')->withCount('projects')->get()->map(function ($department) {
            return [
                'name' => $department->name,
                'employees_count' => $department->employees_count,
                'projects_count' => $department->projects_count
            ];
        });
```

```sample-app/app/Http/Controllers/SqlTrainingController.php
//クエリビルダ
$department = DB::table('departments as d')
    ->select('d.department_name', DB::raw('COUNT(e.id) AS employee_count'), DB::raw('COUNT(p.id) AS project_count'))
    ->leftJoin('employees as e', 'd.id', '=', 'e.department_id')
    ->leftJoin('projects as p', 'd.id', '=', 'p.department_id')
    ->groupBy('d.id')
    ->get();
```

</details>

<details> <summary>問題10: 昇順に並べ替えた従業員リストを取得する</summary>

```sql
SELECT * FROM employees ORDER BY name ASC;
```

```php:Eloquentモデル
$employees = Employee::orderBy('name', 'asc')->get();
```

```php:クエリビルダ
$employees = DB::table('employees')->orderBy('name', 'asc')->get();
```

</details>

<details> <summary>問題11: 従業員数が2人以上の部門を取得する</summary>

```sql
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 5;
```

```php:Eloquentモデル
 $department = Department::withCount('employees')->get()->filter(function ($department) {
            return $department->employees_count >= 2;
        });
        $result = $department->map(function ($department) {
            return [$department->name, $department->employees_count];
        });
```

```php:クエリビルダ
$department = DB::table('departments')
            ->select('departments.name', DB::raw('COUNT(employees.id) as employees_count'))
            ->leftJoin('employees', 'departments.id', '=', 'employees.department_id')
            ->groupBy('departments.id')
            ->having('employees_count', '>=', 2)
            ->get();
```

</details>

<details> <summary>問題12: 一意な役職のリストを取得する</summary>

```sql
SELECT DISTINCT position FROM employees;
```

```php:Eloquentモデル
$employees = Employee::all()->map(function ($employee) {
            return $employee->position;
        })->unique();
```

```php:クエリビルダ
$employees = DB::table('employees')
            ->select('position')
            ->distinct()
            ->get();
```

</details>

<details> <summary>問題13: 最初の10人の従業員を取得する</summary>

```sql
SELECT * FROM employees LIMIT 10;
```


```php:Eloquentモデル
$employees = Employee::query()->limit(10)->get();
```


```php:クエリビルダ
$employees = DB::table('employees')->limit(10)->get();
```
</details>

<details> <summary>問題14: 役職が'Developer'または'Manager'の従業員の名前を取得する</summary>

```sql
SELECT name FROM employees WHERE position IN ('Developer', 'Manager');
```

```php:Eloquentモデル
$employees = Employee::whereIn('position',['Manager','Developer'])->get()->map(function($employee){
            return $employee->name;
        });
```

```php:クエリビルダ
$employees = DB::table('employees')->whereIn('position',['Manager','Developer'])->get()->map(function($employee){
            return $employee->name;
        });
```

</details>

<details> <summary>問題15: 部門ごとのManagerの割合を取得する</summary>

```sql
SELECT department_id, 
       AVG(CASE WHEN position = 'Manager' THEN 1 ELSE 0 END) AS manager_ratio
FROM employees
GROUP BY department_id;
```

```php:Eloquentモデル
$managerRatios = Department::with('employees')->get()->map(function ($department) {
        $managerCount = $department->employees->where('position', 'Manager')->count();
        $totalEmployees = $department->employees->count();
        $managerRatio = $totalEmployees ? $managerCount / $totalEmployees * 100 : 0;

        return [
            'department_id' => $department->id,
            'manager_ratio' => $managerRatio,
        ];
    });
```

```php:クエリビルダ
$managerRatios = DB::table('employees')
    ->select(
        'department_id',
        DB::raw('COUNT(CASE WHEN position = \'Manager\' THEN 1 END) AS manager_count'),
        DB::raw('COUNT(*) AS total_employees'),
        DB::raw('(COUNT(CASE WHEN position = \'Manager\' THEN 1 END) / COUNT(*)) * 100 AS manager_ratio')
    )
    ->groupBy('department_id')
    ->get();
```

</details>

<details> <summary>問題16: 最も多くのプロジェクトに参加している従業員の名前と参加プロジェクト数を取得する</summary>

```sql
SELECT employees.name, COUNT(employee_project.project_id) AS project_count
FROM employees
JOIN employee_project ON employees.id = employee_project.employee_id
GROUP BY employees.id
ORDER BY project_count DESC
LIMIT 1;
```

```php:Eloquentモデル
$employee = Employee::withCount('projects')->orderBy('projects_count', 'desc')->first()->name;
```

```php:クエリビルダ
DB::table('employees')
->join('employee_project', 'employees.id', '=', 'employee_project.employee_id')
->select('employees.name', DB::raw('COUNT(employee_project.project_id) AS project_count'))
->groupBy('employees.id')
->orderBy('project_count', 'desc')
->limit(1)
->get();
```

</details>

<details> <summary>問題17: 名前に「B」が含まれる従業員を取得する</summary>

```sql
SELECT * FROM employees WHERE name LIKE '%B%';
```

```php:Eloquentモデル
$employees = Employee::where('name', 'LIKE', '%B%')->get();
```

```php:クエリビルダ
$employees = DB::table('employees')->where('name', 'LIKE', '%B%')->get();
```

</details>

# 最後に
- 詰まる問題もありましたが引き続き特訓したいと思います
- Eloquentモデルはクエリビルダと比べて直感的に書けることを改めて感じました
- Modelのリレーションの記述の仕方も勉強になりました
- N+1問題とEagerloadingについて深掘りしたいです