こんにちは、Webプログラマの篠田です。
ALAKIでは、Webサービスの開発にLaravelというPHPのフレームワークを活用しています。
今回は、Laravelのクエリビルダを利用して特殊な「サブクエリ」を含むSQLの作成方法をご紹介します。
クエリビルダの基本
Laravelで、SQLを作成する方法はいくつかありますが、その中で「クエリビルダ」を活用したSQLの作成方法というのがあります。
例えば、下記のようなSQLをクエリビルダで表現してみます。
【SQLの場合】
SELECT * FROM users WHERE id=1;
【クエリビルダの場合】
DB::table('users')
->where('id', '=', 1)
->get();
このようにして、クエリビルダを利用することで、生SQLと同等の処理を行うことができます。
FROM句、直後のサブクエリの作り方
今回、問題となったのが下記のようなSQLを、クエリビルダを活用して作成しようとした場合でした。
SELECT * FROM ( SELECT * FROM users WHERE users.id=1 ) AS user LEFT JOIN address ON address.id = user.address_id;
このSQL文は、最初にテーブル「users」を「id=1」で絞り込んでから、テーブル「address」で「LEFT JOIN」してテーブル「users」のレコードを取得するものです。
これをクエリビルダで実現しようとしたときの難所が、最初のFROM句にある「 ( SELECT * FROM users WHERE id=1 ) AS user 」であるサブクエリを表現しないといけない点です。
これをクエリビルダで表現するには、いくつかの工程を行う必要があります。
SQLを分解して作成する
クエリビルダは非常に簡単にSQLを作成することができるのですが、複雑なSQLを記述する場合、いくつかの工程を踏む必要があります。
まずは、「FROM句」で利用するサブクエリ部分の処理を用意します。
$userSql = DB::table('users')
->where('users.id', '=', ':user_id')
->toSql();
今回は、値を安全に取り扱うためプレースホルダを利用した記述方法にしています。
このSQLでは、実際にレコードを取得せず、SQL文のみを「$userSql 」に代入するというのがポイントです。
「toSql」メソッドを利用することで、SQLを実行せずにSQL文だけを作成することができます。
SQLを取り込む
先程、作成したSQL文を保持している変数「$userSql 」を取り込んだSQL文を作成していきます。
DB::table(DB::raw('('.$userSql.') AS user'))
->leftJoin('address', 'address.id', '=', 'user.address_id')
->setBindings([':user_id'=>$user_id])
->get();
通常、「DB::table」メソッドの第1引数には、FROM句に入る「テーブル名」を記入します。
今回は、事前にテーブルとなるサブクエリを「$userSql」が保持しています。
ただ、このままでは引数として使えないので、「DB::raw」メソッドを利用して生SQLを取り込む処理をおこないます。
最終的に「DB::raw」メソッドには「( SELECT * FROM users WHERE users.id=1 ) AS user」となるSQLを引数に持たせることで、FROM句にサブクエリを渡すことができます。
最後に、サブクエリ内にプレースホルダを利用しているので、バインド処理を「setBindings」メソッドを利用することで対応することができます。
まとめ
最初のFROM句に、サブクエリを利用するクエリビルダの書き方が見つからず、悪戦苦闘しました。
Laravelのクエリビルダは非常に便利な機能なので、ドンドン活用できるようにしていきたいですね。
現場の業務フローに寄り添ったWebシステムをお求めなら、私たちALAKIにご相談ください。
ALAKIは経営者様が感じている問題点と、実際にWebシステムを利用される現場スタッフ様が直面している課題を、弊社システムエンジニアが丁寧に聞き取り、お客様と共にシステムを作り上げていきます。
業務改善が実現できるWebシステムをお求めの方は、是非ALAKIにご相談ください。
