〜エンジニア初学者の開発日記〜 六の巻

こんにちは、つかさです
今回もLaravelのクエリビルダについてまとめていこかなと思います。
前々回や前回よりも複雑になってきてるので、一緒に理解できるよう頑張っていきましょう！:grin:

## 前提条件

### Usersテーブル
|id|name|email|status|age|created_at|updated_at|
|:-:|:-:|:-:|:-:|:-:|:-:|:-:|
| 1 | John Doe | john@example.com | active | 22 | 2024-01-01 10:00:00 | 2024-01-02 12:00:00 |
| 2 | Jane Smith | jane@example.com  | active | 30 | 2024-01-03 14:00:00 | 2024-01-04 16:00:00 |
| 3 | Mike Brown | mike@example.com  |inactive| 55 | 2024-02-01 08:30:00 | 2024-02-02 09:45:00 |

## 集計
データの集合から特定の統計的な指標を算出する処理を行うことができます。

### countメソッド
- テーブルのレコードの件数を取得することができる

```php:例
 DB::table('users')->count();
 //3
```

```sql:
SELECT COUNT(*) AS aggregate FROM users;
```

※引数にカラムを指定することができます。その場合、そのカラムが`NULL`でないレコードの件数を取得することになります。

```php:例
 DB::table('users')->count('age');
 //3
```

```sql:
SELECT COUNT(age) AS count_age FROM users;
```

### maxメソッド
- 指定カラムの最大値を取得することができる

```php:例
 DB::table('users')->max('age');
 //55
```

```sql:
SELECT MAX(age) AS max_age FROM users;
```

### minメソッド
- 指定カラムの最小値を取得することができる

```php:例
 DB::table('users')->min('age');
 //22
```

```sql:
SELECT MIN(age) AS min_age FROM users;
```

### avgメソッド
- 指定カラムの平均値を取得することができる

```php:例
 DB::table('users')->avg('age');
```

```sql:
SELECT AVG(age) AS avg_age FROM users;
```

:::note warn
※注意点
小数点以下の桁数はデータベースエンジンに依存します
:::

・MySQLの場合
`int`型なら整数、`DECIMAL(10,2)`型なら小数第2位まで、`float`や`double`型なら、小数点以下が自動的に出力などが挙げられます。

・PostgreSQLの場合
デフォルトでは少数第6桁まで表示されます。

### sumメソッド
- 指定カラムの合計値を取得することができる

```php:例
 DB::table('users')->sum('age');
 //107
```

```sql:
SELECT SUM(age) AS sum_age FROM users;
```

## SELECT
- 取得するカラムを指定することができます
- エイリアスも付けることができます

```php:例
 $users = $userBuilder->select('name', 'email as user_email')->get();
```

```html
 Illuminate\Support\Collection Object
(
    [items:protected] => Array
        (
            [0] => stdClass Object
                (
                    [name] => John Doe
                    [user_email] => john@example.com
                )

            [1] => stdClass Object
                (
                    [name] => Jane Smith
                    [user_email] => jane@example.com
                )

            [2] => stdClass Object
                (
                    [name] => Mike Brown
                    [user_email] => mike@example.com
                )
)
```

:::note warn
※注意点
pluckメソッドとselectメソッドの違いについて理解することは大切です。
:::

#### 返り値の形式
`pluck`:point_right:配列 (Collection のインスタンス) として返される。
`select`:point_right:オブジェクトのリスト（コレクション） として返される。

#### 取得カラム数
`pluck`:point_right:1つのカラム取得可能
`select`:point_right:複数カラム取得可能

#### キー指定	
`pluck`:point_right:可能
`select`:point_right:不可

### addSelectメソッド
- select() メソッドにて取得メソッドを定義した後でさらに取得カラムを追加することができる

```php:例
$userBuilder->select('name', 'email as user_email');

$userBuilder->addSelect('age');

$users = $userBuilder->get();
```

※selectメソッド単体ではデータを取得することはできず、取得するにはgetメソッドが必要です。よってget前であれば、ビルダインスタンスに対して追加することができます。

### distinctメソッド
- 指定したカラムの重複を排除して取得できる

```php:例
$users = User::select('age')->distinct()->get();
```

```sql:
SELECT DISTINCT age FROM users;
```

## SQL文を直接記述

### rawメソッド
- 最も基本的な SQL 文挿入メソッド

```php:例
$users = $userBuilder->select(DB::raw('COUNT(*) AS user_count'))->get();
```
結果: usersテーブルのレコード数を取得します

```html
 [
    {
        "user_count": 50
    }
]

```

データの型は違いますが、取得する値はcountメソッドと同じになります。

```sql:
SELECT COUNT(*) AS user_count FROM users;
```

### selectRawメソッド
- select(DB::raw(...)) 式を置き換えになります

```php:例
$userBuilder->selectRaw('COUNT(*) AS user_count')->get();
```

### whereRawメソッド
- SQLのwhereの部分を直接指定することができます

```php:例
$userBuilder->whereRaw(`age` = 26)->get();
```

```sql:
SELECT * FROM `users` WHERE age = 26;
```

#### バインディングを使用した書き方

```php:例
$userBuilder->whereRaw('age = ?', [26])->get();
```

##### バインディングを使うのか？
:point_right:whereRawに直接値を埋め込むと、`SQLインジェクション`のリスクがある。バインディングを使うことで、Laravelが自動的に`エスケープ`してくれるため、安全にSQLを実行できるようになる。

:::note info
SQLインジェクションとは？
ユーザーからの入力をそのままSQLクエリに組み込んでしまい、悪意あるSQLコードが実行されてしまうセキュリティ上の脆弱性
:::

:::note info
エスケープとは？
SQLやプログラムコードで特別な意味を持つ文字を、単なる文字列として扱うために無害化するプロセス
:::

### groupByRaw メソッド
- groupByメソッドの値を指定することができる

#### そもそもgroupByメソッドの使い方は？

```php:例
$users = DB::table('users')
            ->select('age', DB::raw('count(*) as user_count'))
            ->groupBy('age')
            ->get();
```

```sql:
SELECT age, COUNT(*) as user_count FROM users GROUP BY age;
```
結果例
```html
Illuminate\Support\Collection Object
(
    [items:protected] => Array
        (
            [0] => stdClass Object
                (
                    [count] => 1
                    [age] => 33
                )

            [1] => stdClass Object
                (
                    [count] => 3
                    [age] => 47
                )

            [2] => stdClass Object
                (
                    [count] => 2
                    [age] => 48
                )

            [3] => stdClass Object
                (
                    [count] => 3
                    [age] => 27
                )
            .
            .
            .
        )
)
```

### havingRaw メソッド
- havingメソッドの値を指定することができる

#### SQLでのhaving句の役割
`WHERE`はグループ化する前に行に対して条件を指定します。
`HAVING`はグループ化された後の集計結果に対して条件を指定します。

```php:例
$userBuilder->select('prefecture_id', 'age', DB::raw('COUNT(age) as number_age'))
            ->groupBy('prefecture_id', 'age')
            ->havingRaw('age >= 30')
            ->get();
```

```sql:
SELECT prefecture_id, age, COUNT(age) as number_age
FROM users
GROUP BY prefecture_id, age
HAVING age >= 30;
```
### orderByRaw メソッド
- orderByメソッドの値を指定することができる

```php:例
$userBuilder->orderByRaw('created_at DESC')->get();
```

```sql:
SELECT * FROM users
ORDER BY created_at DESC;
```

## まとめ
今回はクエリビルダの集計とsqlを直接入力するタイプのメソッドについて整理しました。普段はsqlを意識せずに取得できてしまいますが、内部でどのようなsqlが実行されているかまで理解することで、より視野が広がると思います。
クエリビルダは範囲が広いので複数の記事で分割して投稿していく予定です。
読んでいただきありがとうございました！