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

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

前提条件

Usersテーブル

id	name	email	status	age	created_at	updated_at
1	John Doe	[e-mail]	active	22	2024-01-01 10:00:00	2024-01-02 12:00:00
2	Jane Smith	[e-mail]	active	30	2024-01-03 14:00:00	2024-01-04 16:00:00
3	Mike Brown	[e-mail]	inactive	55	2024-02-01 08:30:00	2024-02-02 09:45:00

集計

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

countメソッド

• テーブルのレコードの件数を取得することができる

例

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

SELECT COUNT(*) AS aggregate FROM users;

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

例

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

SELECT COUNT(age) AS count_age FROM users;

maxメソッド

• 指定カラムの最大値を取得することができる

例

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

SELECT MAX(age) AS max_age FROM users;

minメソッド

• 指定カラムの最小値を取得することができる

例

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

SELECT MIN(age) AS min_age FROM users;

avgメソッド

• 指定カラムの平均値を取得することができる

例

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

SELECT AVG(age) AS avg_age FROM users;

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

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

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

sumメソッド

• 指定カラムの合計値を取得することができる

例

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

SELECT SUM(age) AS sum_age FROM users;

SELECT

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

例

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

 Illuminate\Support\Collection Object
(
    [items:protected] => Array
        (
            [0] => stdClass Object
                (
                    [name] => John Doe
                    [user_email] => [e-mail]
                )

            [1] => stdClass Object
                (
                    [name] => Jane Smith
                    [user_email] => [e-mail]
                )

            [2] => stdClass Object
                (
                    [name] => Mike Brown
                    [user_email] => [e-mail]
                )
)

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

返り値の形式

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

取得カラム数

pluck1つのカラム取得可能
select複数カラム取得可能

キー指定

pluck可能
select不可

addSelectメソッド

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

例

$userBuilder->select('name', 'email as user_email');

$userBuilder->addSelect('age');

$users = $userBuilder->get();

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

distinctメソッド

• 指定したカラムの重複を排除して取得できる

例

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

SELECT DISTINCT age FROM users;

SQL文を直接記述

rawメソッド

• 最も基本的な SQL 文挿入メソッド

例

$users = $userBuilder->select(DB::raw('COUNT(*) AS user_count'))->get();

結果: usersテーブルのレコード数を取得します

 [
    {
        "user_count": 50
    }
]

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

SELECT COUNT(*) AS user_count FROM users;

selectRawメソッド

• select(DB::raw(...)) 式を置き換えになります

例

$userBuilder->selectRaw('COUNT(*) AS user_count')->get();

whereRawメソッド

• SQLのwhereの部分を直接指定することができます

例

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

SELECT * FROM `users` WHERE age = 26;

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

例

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

バインディングを使うのか？

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

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

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

groupByRaw メソッド

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

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

例

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

SELECT age, COUNT(*) as user_count FROM users GROUP BY age;

結果例

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はグループ化された後の集計結果に対して条件を指定します。

例

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

SELECT prefecture_id, age, COUNT(age) as number_age
FROM users
GROUP BY prefecture_id, age
HAVING age >= 30;

orderByRaw メソッド

• orderByメソッドの値を指定することができる

例

$userBuilder->orderByRaw('created_at DESC')->get();

SELECT * FROM users
ORDER BY created_at DESC;

まとめ

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