Go to Qiita Advent Calendar 2024 Top
LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@yamatai12

Laravelでsqlの特訓をする

Last updated at Posted at 2024-10-31

以下の記述を比較できるようまとめました

  • 素のsql
  • Eloquentモデル
  • クエリビルダ

背景

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

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

テーブルのER図

テーブル

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モデルを作成します。

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モデルを作成します。

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モデルを作成します。

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練習問題

問題1: 役職がManagerである従業員の数をカウントする 
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();
問題2: 従業員の名前と所属する部門の名前を一緒に取得する 
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,
            ];
        });

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();
問題3: Project Aに参加している従業員のリストを取得する 
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();
問題4: 部門名ごとの従業員の数をカウントする 
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();

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();
問題5: プロジェクト Bに参加している全従業員の役職を取得する 
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();
問題6: 部門ごとのプロジェクトの数をカウントする 
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();
問題7: 従業員のBobが参加しているプロジェクトのリストを取得する 
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;
            });
問題8: 役職ごとの従業員のリストを取得する 
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();
問題9: 部門ごとの従業員とプロジェクトの数を取得する 
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();
問題10: 昇順に並べ替えた従業員リストを取得する 
SELECT * FROM employees ORDER BY name ASC;
Eloquentモデル
$employees = Employee::orderBy('name', 'asc')->get();
クエリビルダ
$employees = DB::table('employees')->orderBy('name', 'asc')->get();
問題11: 従業員数が2人以上の部門を取得する 
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 5;
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];
        });
クエリビルダ
$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();
問題12: 一意な役職のリストを取得する 
SELECT DISTINCT position FROM employees;
Eloquentモデル
$employees = Employee::all()->map(function ($employee) {
            return $employee->position;
        })->unique();
クエリビルダ
$employees = DB::table('employees')
            ->select('position')
            ->distinct()
            ->get();
問題13: 最初の10人の従業員を取得する 
SELECT * FROM employees LIMIT 10;
Eloquentモデル
$employees = Employee::query()->limit(10)->get();
クエリビルダ
$employees = DB::table('employees')->limit(10)->get();
問題14: 役職が'Developer'または'Manager'の従業員の名前を取得する 
SELECT name FROM employees WHERE position IN ('Developer', 'Manager');
Eloquentモデル
$employees = Employee::whereIn('position',['Manager','Developer'])->get()->map(function($employee){
            return $employee->name;
        });
クエリビルダ
$employees = DB::table('employees')->whereIn('position',['Manager','Developer'])->get()->map(function($employee){
            return $employee->name;
        });
問題15: 部門ごとのManagerの割合を取得する 
SELECT department_id, 
       AVG(CASE WHEN position = 'Manager' THEN 1 ELSE 0 END) AS manager_ratio
FROM employees
GROUP BY department_id;
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,
        ];
    });
クエリビルダ
$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();
問題16: 最も多くのプロジェクトに参加している従業員の名前と参加プロジェクト数を取得する 
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;
Eloquentモデル
$employee = Employee::withCount('projects')->orderBy('projects_count', 'desc')->first()->name;
クエリビルダ
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();
問題17: 名前に「B」が含まれる従業員を取得する 
SELECT * FROM employees WHERE name LIKE '%B%';
Eloquentモデル
$employees = Employee::where('name', 'LIKE', '%B%')->get();
クエリビルダ
$employees = DB::table('employees')->where('name', 'LIKE', '%B%')->get();

最後に

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?