こんにちは、sugoです。

今回は、Laravelのクエリビルダの学習したいと思い、私が行った学習方法についてまとめたいと思います。

どのように学習しようかと考えた際に、ChatGPTを活用して学習を行いました。

SQL文は書けるが、Laravelのクエリビルダの理解があまりできていないという方に、少しでも参考になればと思います。

背景

まずは、なぜクエリビルダの学習をしようと思ったのか、その背景を説明します。

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

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

そこで、Laravelでのデータ取得に関する処理をもっと理解し、学びを増やすため、私はこの学習を始め、今回の記事を書くことにしました。

また、近年ChatGPTが話題になっていることから、ChatGPTを活用して、何かしたいという考えから、ChatGPTを活用した学習をすることにしました。

学習方法

ChatGPTにSQL文を作成してもらい、そのSQL文をLaravelのクエリビルダに書き直していくという方法で学習を進めました。

以下が学習の流れです。

• ChatGPTにいくつかの条件をつけたSQL文を作成してもらう。
• そのSQL文をLaravelのクエリビルダに書きかえて、学習を進める。
  • Laravelのクエリビルダに関するドキュメントを参考にしながら、クエリビルダに書きかえていく。
• デバッグを用いて、問題のSQL文と一致するか確認。

ChatGPTが作成したSQL文

顧客情報、注文情報、商品情報、支払い情報、および配送情報を含む5つのテーブルをJOINし、特定の条件で結果をフィルタリングします。さらに、サブクエリを使用して、特定の条件を持つ顧客の支払い総額を計算します。

SELECT 
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.order_date,
    p.product_name,
    p.product_price,
    s.payment_method,
    s.total_amount,
    d.shipping_address
FROM 
    customers c
JOIN 
    orders o ON c.customer_id = o.customer_id
JOIN 
    order_details od ON o.order_id = od.order_id
JOIN 
    products p ON od.product_id = p.product_id
JOIN 
    payments s ON o.order_id = s.order_id
JOIN 
    shipments d ON o.order_id = d.order_id
WHERE 
    c.customer_region = 'Asia'
    AND p.product_category = 'Electronics'
    AND o.order_date >= '2024-01-01'
    AND o.order_date <= '2024-05-01'
    AND s.total_amount > (
        SELECT 
            AVG(total_amount)
        FROM 
            payments
        WHERE 
            payment_method = 'Credit Card'
    );

クエリビルダに変換

上記で作成されたSQL文を、クエリビルダに変換すると以下のようになります。

Laravelのクエリビルダについてまとめられているサイトを参考に変換を行いました。

$paymentsAvg = DB::table('payments')
    ->where('payment_method', 'Credit Card')
    ->avg('total_amount');

$query = DB::table('customers as c')
    ->select(
        'c.customer_id',
        'c.customer_name',
        'o.order_id',
        'o.order_date',
        'p.product_name',
        'p.product_price',
        's.payment_method',
        's.total_amount',
        'd.shipping_address'
    )
    ->join('orders as o', 'c.customer_id', '=', 'o.customer_id')
    ->join('order_details as od', 'o.order_id', '=', 'od.order_id')
    ->join('products as p', 'od.product_id', '=', 'p.product_id')
    ->join('payments as s', 'o.order_id', '=', 's.order_id')
    ->join('shipments as d', 'o.order_id', '=', 'd.order_id')
    ->where('c.customer_region', 'Asia')
    ->where('p.product_category', 'Electronics')
    ->whereBetween('o.order_date', ['2024-01-01', '2024-05-01'])
    ->where('s.total_amount', '>', $paymentsAvg)
    ->get();

変換後のクエリビルダが、ChatGPTに作成してもらったSQL文と同様か確認を行います。

ddRawSqlメソッドを用いてデバッグを行い、実行されるSQL文を表示し確認を行います。

デバッグ結果は以下のようになりました。

select 
    `c`.`customer_id`, 
    `c`.`customer_name`, 
    `o`.`order_id`, 
    `o`.`order_date`, 
    `p`.`product_name`, 
    `p`.`product_price`, 
    `s`.`payment_method`, 
    `s`.`total_amount`, 
    `d`.`shipping_address` 
from 
    `customers` as `c` 
inner join 
    `orders` as `o` on `c`.`customer_id` = `o`.`customer_id` 
inner join 
    `order_details` as `od` on `o`.`order_id` = `od`.`order_id` 
inner join 
    `products` as `p` on `od`.`product_id` = `p`.`product_id` 
inner join 
    `payments` as `s` on `o`.`order_id` = `s`.`order_id` 
inner join 
    `shipments` as `d` on `o`.`order_id` = `d`.`order_id` 
where 
    `c`.`customer_region` = 'Asia' 
    and `p`.`product_category` = 'Electronics' 
    and `o`.`order_date` >= '2024-01-01' 
    and `o`.`order_date` <= '2024-05-01'
    and `s`.`total_amount` > '1425.000000'

このことから、作成したいSQL文と同じクエリビルダを作成できました。

以下からは、SQL文をクエリビルダに変換し、上記のクエリビルダに辿り着くまでの学びを挙げていきます。

エイリアスについて

クエリビルダだとエイリアスの指定ができないと思っていたのですが、簡単に書けることを学びました。

カラム名 as エイリアスで書くだけで簡単だったので、今後クエリビルダを使う際は、エイリアスの利用も考えて使っていきたいと思います。

今回のSQLは、SELECT文やJOINすることが多かったので、かなり書く内容を減らすことができ重宝しました。

サブクエリについて

今回のSQLは、サブクエリを用いていたのですが、クエリビルダに変換する際は、一度サブクエリのSQLを実行し、取得した値を変数として渡して実行しました。

バックエンドで実行する際は、2回実行しても返す値は変わらないので、今回は2回に分けて実行しました。

いつもサブクエリが出るとあまり理解できていなかったのですが、今回のように2回に分けるイメージで考えると、簡単に理解できそうです。

whereでクロージャを用いれば以下のように一回で書くこともできます。

->where('s.total_amount', '>', function ($query) {
        $query->selectRaw('AVG(total_amount)')
            ->from('payments')
            ->whereRaw('payment_method = "Credit Card"');
    })

まとめ

今回はChatGPTを使ってLaravelのクエリビルダの学習を行いました。

実際にSQL文をクエリビルダに変換することで、いままでなんとなく書いていた部分の理解が深まりました。

またAIを活用することで、学習時間を大幅に短縮できました。

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