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

こんにちは、つかさです
今まで数回Laravelのクエリビルダについての記事を投稿してきましたが、1番使う機会も多く便利な`Where`についてまとめたいと思います。
whereだけでもかなりのバリエーションがあるので、一緒に理解できるよう頑張っていきましょう！: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 |
| 4 | SUZUKI Taro | trao@example.com  |inactive| 40 | 2024-02-10 09:30:00 | 2024-03-02 12:40:00 |

### Postsテーブル
|id|user_id|title|content|status|created_at|updated_at|
|:-:|:-:|:-:|:-:|:-:|:-:|:-:|
| 1 | 1 | 初めての投稿 | これは最初の投稿です。| published | 2024-06-01 12:00:00 | 2024-06-01 12:00:00 |
| 2  | 1 | 旅行の思い出 | 先週の旅行について書きました。| draft | 2024-06-05 14:30:00 | 2024-06-06 10:15:00 |
| 3  | 2 | Laravelの使い方 | Laravelの基本を説明します。| published | 2024-06-10 09:00:00 | 2024-06-10 09:00:00 |
| 4  | 3 | 今日の出来事 | 今日は良い一日でした。| published | 2024-06-15 18:20:00 | 2024-06-15 18:45:00 |
| 5  | 5 | Vue.jsの基本 | Vue.jsの使い方を学ぼう。| draft | 2024-06-20 11:10:00 | 2024-06-21 08:30:00 |


## Where 
クエリに対して条件を付与することができる。

### シンプルなwhereメソッド

```php:例
 $users = $userBuilder->where('id', '>', 3)
                     ->get();
```
- 第一引数 : カラム名
- 第二引数 : 比較演算子
- 第三引数 : 比較する値

| 演算子 | 説明 | 例（Laravel） | 変換される SQL |
|--------|------|--------------|---------------|
| `=`    | 等しい | `where('status', '=', 'active')` | `WHERE status = 'active'` |
| `!=` / `<>` | 等しくない | `where('status', '!=', 'inactive')` | `WHERE status != 'inactive'` |
| `>`    | より大きい | `where('age', '>', 18)` | `WHERE age > 18` |
| `<`    | より小さい | `where('age', '<', 65)` | `WHERE age < 65` |
| `>=`   | 以上 | `where('score', '>=', 80)` | `WHERE score >= 80` |
| `<=`   | 以下 | `where('score', '<=', 50)` | `WHERE score <= 50` |
| `LIKE` | 部分一致検索（大文字小文字を区別） | `where('name', 'LIKE', '%John%')` | `WHERE name LIKE '%John%'` |
| `NOT LIKE` | 一致しない | `where('email', 'NOT LIKE', '%@gmail.com')` | `WHERE email NOT LIKE '%@gmail.com'` |
| `ILIKE` | 部分一致（PostgreSQLのみ・大文字小文字区別なし） | `where('title', 'ILIKE', '%laravel%')` | `WHERE title ILIKE '%laravel%'` |

:::note info
よく見る`$userBuilder->where('id',  10)`というのは`$userBuilder->where('id', = , 10)`を省略したものになります。
:::

#### And条件

:bulb:whereメソッドを繋げることで`And条件`になります。

```php:連続型
 $userBuilder->where('email',  'like', '%.org')
            ->where('id','<', 10)
```
また以下のようにも書けます！

```php:配列型
 $userBuilder->where([
    ['email',  'like', '%.org'],
    ['id','<', 10],
])
```

:::note warn
この２つの結果は同じですが、厳密には行われている処理に違いがあります。
連続型はwhere() を1つずつ適用するのに対し、配列型は配列を受け取り、一括適用します。
:::

#### Or条件

:bulb:`Or条件`場合は orWhere メソッドを使います。

```php:例
$userBuilder->where('email',  'like', '%.org')
            ->orWhere('id','<', 10)
```

#### 範囲条件

##### whereBetween / orWhereBetween

```php:例
$userBuilder->whereBetween('id', [4, 6])->get();
```

上記の例ではidが4,5,6のレコードが取得できます。

```php:例
$userBuilder->whereBetween('id', [4, 6])->orWhereBetween('age', [20, 30])->get();
```
`orWhereBetween`は範囲のOr条件になります。

##### whereNotBetween / orWhereNotBetween
- `Between`の前にNotをつけることで、指定した条件の否定が適応されます

```php:例
$userBuilder->whereNotBetween('id', [4, 6])->get();
```

##### whereBetweenColumns / whereNotBetweenColumns / orWhereBetweenColumns / orWhereNotBetweenColumns
- 第一引数に指定したカラムの値が、第二引数で渡すカラムリストの間にあるかの条件を付与
```php:例
$userBuilder->whereBetweenColumns('weight', [
    'minimum_allowed_weight', 
    'maximum_allowed_weight',
])
```

| id  | name    | weight | minimum_allowed_weight | maximum_allowed_weight |
|----|--------|--------|----------------------|----------------------|
| 1  | Alice  | 5      | 3                    | 8                    |
| 2  | Bob    | 2      | 3                    | 8                    |
| 3  | Charlie | 7      | 5                    | 10                   |
| 4  | Dave   | 9      | 6                    | 9                    |

テーブルに上記のようなデータがあった場合、結果は以下のようになります。

- Alice: 5 は 3 <= weight <= 8 ✅ 取得
- Bob: 2 は 3 <= weight <= 8 ❌ 取得されない
- Charlie: 7 は 5 <= weight <= 10 ✅ 取得
- Dave: 9 は 6 <= weight <= 9 ✅ 取得

:bulb:`or条件`や`否定`は`whereBetween`の時と同様です。

#### 含んでいるか条件

##### whereIn / orWhereIn
- 第二引数で指定した値を含んでいるかの条件を付与する事ができます
```php:例
$userBuilder->whereIn('id', [2, 5, 9])
```

:::note info
ちなみに`$userBuilder->whereIn('id', [2, 5, 9])`は`$userBuilder->where('id', 2)->orWhere('id', 5)->orWhere('id', 9)`のショートカット版という覚え方でも良い:ok_hand:
:::

##### whereNotIn / orWhereNotIn
- whereInの反対の条件になります
```php:例
$userBuilder->whereNotIn('id', [2, 5, 9])
```

:::note warn
whereInメソッドに指定する値は、`完全一致`でなければならないことに注意!
:::

#### NULL判別条件

##### whereNull / orWhereNull
- 指定したカラムの値がnullであるかどうかの条件になります
```php:例
$userBuilder->whereNull('email')
```

##### whereNotNull / orWhereNotNull
- whereNullの反対の条件になります

```php:例
$userBuilder->whereNotNull('email')
```

#### 日時条件

##### whereDate / orWhereDate
- 日付の条件になります
```php:例
$userBuilder->whereDate('created_at', '<', '2024-01-30')
```

##### whereMonth / orWhereMonth
- 月の条件になります
```php:例
$userBuilder->whereMonth('created_at', '=', 1)
```

##### whereDay / orWhereDay
- 年度や月を指定せず、シンプルな日付の条件になります
```php:例
$userBuilder->whereDay('created_at', '=', 12)
```

##### whereYear / orWhereYear
- 年の条件になります
```php:例
$userBuilder->whereYear('created_at', '=', 2024)
```

##### whereTime / orWhereTime
- 時間の条件になります
```php:例
$userBuilder->whereYear('created_at', '=', 2024)
```

##### whereColumn / orWhereColumn
- 2つのカラムを比較する条件になります
```php:例
$userBuilder->whereColumn('created_at', '=', 'updated_at')
```

<details open><summary>問題</summary>
ちなみに上記のクエリはどのようなユーザーを取得しようとしているでしょうか？
</details>

<details><summary>答え</summary>
まだ一度も編集や更新をしていないユーザーです。何故ならユーザー作成時にcreated_atとupdated_atには同じ時間が代入されます。その後、更新処理があればupdated_atの時間だけが更新されていきます。
</details>

ちなみに比較の条件は複数指定できます。
```php:例
$userBuilder->whereColumn([
    ['email_verified', '>', 'updated_at'],
    ['created_at', '<', 'updated_at']
])
```

#### 存在条件

##### Where Exists
- サブクエリが 結果を返す場合に true となる条件です

```php:例
$userBuilder->whereExists(function (Builder $query) {
                  $query->select(DB::raw(1))
                        ->from('posts')
                        ->whereColumn('posts.user_id', 'users.id');
            })
```
上記の条件を言語化すると以下になります。
「少なくとも1つの投稿（posts）を持つユーザー（users）だけを取得する」という条件

```sql:例
SELECT * FROM users
WHERE EXISTS (
    SELECT 1 FROM posts
    WHERE posts.user_id = users.id
);
```

:::note warn
ちなみにこのようなクエリを書くことはできません。
$userBuilder->whereExists('posts.user_id', '=', 'users.id'); // ❌ NG
whereExists() の引数には 関数（サブクエリビルダー） しか使えません。
:::

#### JSON型のカラムに対する条件

##### whereJsonContains / orWhereJsonContains

- 配列だった場合
```json:rolesの配列
["admin", "editor", "user"]
```
```php:例
$userBuilder::whereJsonContains('roles', 'editor')->get();
```

```sql:例
SELECT * FROM users WHERE JSON_CONTAINS(roles, '"editor"');
```

この例では、`editor`がrolesカラムのjosn型の値にある場合は取得するようなクエリになります。

- オブジェクトだった場合
```json:rolesのオブジェクト
{
  "features": {
    "newsletter": true,
    "beta": false
  }
}

```
```php:例
User::whereJsonContains('features->newsletter', true)->get();
```

```sql:例
select * from `users`
where json_extract(`features`, '$.newsletter') = true;
```

##### whereJsonLength / orWhereJsonLength
- JSON カラム内の配列の要素数を条件になります

```json:rolesの配列
["PHP", "Laravel", "MySQL"]
```
```php:例
User::whereJsonLength('skills', 3)->get();
```

```sql:例
select * from `users`
where json_length(`skills`) = 3;
```

### まとめ

読んでいただきありがとうございました！
これからも自分のペースで記事を更新したり、新たな記事を書いていこうと思うので是非見ていただけたらなと思います！:grin: