こんにちは。sugoです。

今回は、Laravelクエリビルダの学習 第二弾となります。

前回はChatGPTにSQLを出してもらい、クエリビルダに変換し学習を行いました。Laravelのクエリビルダの勉強をするために、ChatGPTを使って学習してみた

より数をこなし、理解を深めるために、MySQLの新卒研修に使っているSQLをクエリビルダに変換してみました。

Laravelのクエリビルダの学習を行っている方に、少しでも参考になればと思います。

背景

まずは、なぜ今回の記事を書こうかと思ったかを説明します。

私は現在、PHPのフレームワークであるLaravelを使用しています。

しかし、Laravelでのデータ取得処理については、クエリビルダかEloquentかを選ぶ段階で、調べないと読み書きできないレベルでした。

そこで、前回の記事にて、ChatGPTを活用して、基礎的な部分の学習を行いました。

今回は、基礎を固めて実践できるように、LaravelでMySQLの問題を解いたときのことを記事にしました。

学習の進め方

ウエディングパークでは新卒研修で、DBとは何かから始まり、実際にSQL文を読み書きして学んでいくMySQL研修があります。

研修の中で、さまざまなSQL文が問題として用意されているので、そのSQLをクエリビルダに変換し、学習を行うことにしました。

なので、今回の記事はSQL文をどんどんクエリビルダに変換していきたいと思います。

問題1

「食品テーブルの食品タイプIDが3の食品タイプ名, 食品名の一覧」

SELECT ft.name, f.name
FROM foods f
INNER JOIN food_types ft ON f.food_type_id = ft.id
WHERE f.food_type_id = 3;

SQLをクエリビルダに変換します。

DB::table('foods as f')
    ->select('ft.name as food_type_name', 'f.name as food_name')
    ->join('food_types as ft', 'f.food_type_id', '=', 'ft.id')
    ->where('f.food_type_id', 3)
    ->get();

join句はjoinメソッドを用いて、where句は、whereメソッドを使います。

クエリビルダに変換する際に、AS句を設け、二つの結果が返ってくるようにしています。

問題2

「値段が300以上の食品をもつ食品タイプIDと食品タイプ名の一覧」

SELECT ft.id, ft.name
FROM foods f
INNER JOIN food_types ft ON f.food_type_id = ft.id
WHERE f.price >= 300;

SQLをクエリビルダに変換します。

DB::table('foods as f')
    ->select(
        'ft.id',
        'ft.name',
    )
    ->join('food_types as ft', 'f.food_type_id', '=', 'ft.id')
    ->where('f.price', '>=', 300)
    ->get();

join句とwhere句は問題1と同様に、joinメソッド、whereメソッドを使います。

比較演算子が=ではなく、>=なので第二引数に比較演算子を指定しています。

問題3

「食品テーブルの食品の金額を食品タイプごとに合計して、1000円以上になる食品タイプ名を抽出」

SELECT ft.name, SUM(f.price)
FROM foods f
INNER JOIN food_types ft
  ON ft.id = f.food_type_id
GROUP BY food_type_id
HAVING SUM(f.price) >= 1000;

SQLをクエリビルダに変換します。

DB::table('foods as f')
    ->select('ft.name')
    ->selectRaw('SUM(f.price)')
    ->join('food_types as ft', 'ft.id', '=', 'f.food_type_id')
    ->groupBy('food_type_id')
    ->havingRaw('SUM(f.price) >= 1000')
    ->get();

クエリビルダでSUMを取得する際は、selectRawメソッドを使いました。selectRawメソッドは、素のSQL式を挿入できます。

グループ化を行うGROUP BY、条件を絞り込むHAVINGは、groupByメソッドとhavingメソッドを使います。

今回havingは、SUMのデータで条件を絞り込むため、havingRawメソッドを使い、素のSQL式を挿入しました。

問題4

「買いものリストの個数が最も多い食品IDを抽出」

SELECT food_id AS '食品ID'
FROM shoppings
WHERE number  = (
  SELECT MAX(number) FROM shoppings
);

SQLをクエリビルダに変換します。

DB::table('shoppings')
    ->select('food_id AS 食品ID')
    ->where('number', '=', function ($query) {
        $query->selectRaw('MAX(number)')
            ->from('shoppings');
    })
    ->get();

where句にサブクエリが必要なので、クロージャを用いています。

クロージャ内で、MAXの値を求めるために、selectRawを用いて、素のSQL式でMAXを挿入します。

問題5

「2022年5月11日時点で賞味期限が切れているものがない食品タイプを抽出」

SELECT 
  ft.name AS '食品タイプ名'
FROM 
  food_types ft
LEFT JOIN (
  SELECT food_type_id
  FROM foods
  WHERE 
    expire_date < '2022-05-11'
) sub ON sub.food_type_id = ft.id
WHERE sub.food_type_id IS NULL;

SQLをクエリビルダに変換します。

$sub = DB::table('foods')
    ->select('food_type_id')
    ->where('expire_date', '<', '2022-05-11');

DB::table('food_types as ft')
    ->select('ft.name AS 食品タイプ名')
    ->leftJoinSub($sub, 'sub', function (\Illuminate\Database\Query\JoinClause $join) {
        $join->on('sub.food_type_id', '=', 'ft.id');
    })
    ->whereNull('sub.food_type_id')
    ->get();

LEFT JOINでサブクエリを実行するために、サブクエリのビルダーを代入する。

leftJoinSubメソッドを使い、第一引数にサブクエリのビルダーを指定し、第二引数にエイリアスを指定します。

where句のIS NULLは、whereNullメソッドを使います。

まとめ

今回は新卒研修のMySQLのSQLを、Laravelのクエリビルダに変換し学習を行いました。

基礎的なメソッドや、初めて使うメソッドがあったりと書きながら知識を深めることができました。

クエリビルダのメソッドは、他にもさまざまなものがあるため、その内容についても学習をしていきたいと思います。

最後までお読みいただきありがとうございました。

Join Us !

ウエディングパークでは、一緒に働く仲間を募集しています！
ご興味ある方は、お気軽にお問合せください（カジュアル面談から可）

採用情報を見る