メインコンテンツまでスキップ

クエリビルダ

データベースからレコードを取得する際、SQL文を組み立てて問い合わせをします。
この組み立てを簡単にできる機能のことをクエリビルダといいます。
LaravelやcakePHPなどのフレームワークが取り入れていますが、ここではLaravelでの使用方法に限定していこうと思います。

Laravelでのデータ操作は、「クエリビルダ」、「Eloquent」、「SQL」の３つの方法があります。
これらの実装方法にはどのような違いがあるのでしょうか。

クエリビルダとEloquentとSQLの違いについて​

それぞれの違いについて、以下のデータを使用して確認してみましょう。

usersテーブル

idnameemailtelbirthdayaddress
1田中 太郎taro@test.jp080-1234-56782000-01-01沖縄
2佐藤 二郎jiro@test.jp090-8765-43211969-05-07東京
3北島 三郎saburo@test.jp090-1111-00001936-10-04東京

クエリビルダ​

DBファサードを使用して、テーブルに対して直接データベース操作を行う実装方法です。
クエリビルダを利用する場合は、DBクラスのtable()メソッドを使用する必要があります。

メリット： 柔軟性が高いため、複雑なクエリを書くことができる。（複数のテーブル結合やサブクエリの作成などに有効的）
デメリット： メソッドを連鎖的（チェーン状）に書くため、SQLを直接書くよりは可読性があるが同じクエリを何度も書く必要があるため冗長的になりやすい。
返却値： Illuminate\Support\Collectionオブジェクト

クエリビルダで書く全件取得
use Illuminate\Support\Facades\DB;

$users = DB::table('users')->get();

Eloquent​

Modelディレクトリに作成されたモデルクラスを使用して、モデルクラスに対してデータベース操作を行う実装方法です。
※ORM（Object-Relational Mapping）の一種

メリット： クエリビルダの全機能を内包し、可読性と保守性の面で優れている。（リレーション・アクセサ・ミューテータ等の豊富な機能がある）
デメリット： シンプルなクエリを作成するのに適しているため、複雑なクエリの場合は不向き。
返却値： Illuminate\Database\Eloquent\Collectionオブジェクト

Eloquentで書く全件取得
use App\Models\User;

$users = User::all();

SQL​

DBファサードを使用して、直接SQL言語でデータベース操作を行う実装方法です。
最も直接的にデータベース操作を行う方法ですが、一つ一つ処理を記述する必要があります。

メリット： パフォーマンスが高い。※クエリビルダやEloquentではカバーできないような特殊ケース（MySQLなどのDB管理システム固有のメソッド使用等）の場合に使用するのが良い。
デメリット： 直接データベースに対してクエリを実行するため、再利用性がない。クエリが長くなりやすいため、可読性・保守性が低下する傾向にある。
返却値： stdClassオブジェクト

SQLで書く全件取得
use Illuminate\Support\Facades\DB;

$users = DB::select('select * form users');

３つの実装方法について「パフォーマンス」・「可読性と保守性」・「柔軟性」の点で簡単にまとめると以下になります。

まず「パフォーマンス」ですが、この３つだと「SQL」が最も高い実装方法になります。
それは、直接データベースに対してクエリを実行するため、中間層を介さずにデータを取得でき、結果として処理速度が向上します。
「クエリビルダ」や「Eloquent」は、SQLを抽象化するための追加の処理が行われており、クエリ実行に少なからずオーバーヘッド（クエリ実行以外の余分な処理）が発生します。

次に「可読性と保守性」ですが、こちらは「Eloquent」が比較的高い実装方法になります。
クエリビルダの全機能を内包し、 リレーション、アクセサ、ミューテータなどの豊富な機能があるためシンプルなクエリ作成に適しています。
「クエリビルダ」や「SQL」は、直感的にコードを理解することが難しく、同じクエリを何度も書く場合があるため冗長的になりやすいです。

最後に「柔軟性」は、「クエリビルダ」が有効な実装方法になります。
クエリの一部を再利用したり、プログラムの状態に基づいてクエリを変更したりすることが容易にできます。
「SQL」は、柔軟性は高いですが可読性や保守性を低下させるので使用を避けた方が良いでしょう。

それぞれにメリット・デメリットがあるため、プロジェクトの要件に合った最適な実装方法を考えることが重要です。
それでは、実際にクエリビルダを使った基本的なデータ操作の実装方法をみていきましょう。

基本的なデータ操作​

クエリビルダを活用すればほとんどのSQLは構築することができます。
以下の表は基本的なクエリビルダのメソッド一覧です。

メソッド構文返り値(型)
getget('フィールド名')Collection全てのレコードが返る ※引数指定の場合、指定フィールドの値のみ返る
firstfirst()Collectionレコード1件が返る ※データなしの場合、nullが返る
selectselect('フィールド1', 'フィールド2', ...)---取得したいカラムを指定する ※遅延実行1
pluckpluck('フィールド名')Collection指定カラムのコレクションが返る ※データなしの場合、空が返る
insertinsert(['フィールド1' => 値])boolean成功の結果が返る
updateupdate(['フィールド1' => 値])integer影響を与えたレコード数が返る ※データなしの場合、0が返る
deletedelete(['フィールド1' => 値])integer影響を与えたレコード数が返る ※データなしの場合、0が返る
upsertupsert(
[挿入または更新するレコードの配列],
[一意のカラム名またはカラム名の配列],
[更新するカラム名またはカラム名の配列]
)		integer影響を与えたレコード数が返る ※第１引数に第２引数のユニークキーがない場合は挿入処理になる
wherewhere('フィールド名', 値) or where('フィールド名', '演算記号', 値)---取得したいレコードを指定する ※遅延実行1
orderByorderBy('フィールド名', asc または desc)---レコードを指定カラムで並び替える ※遅延実行1
groupBygroupBy('フィールド名')---指定カラムでグルーピングする ※遅延実行1

リンク：https://github.com/laravel/framework/blob/10.x/src/Illuminate/Database/Query/Builder.php

データの取得、挿入、削除、並び替え、グルーピングなど使用頻度が高いクエリビルダをピックアップしてみました。
SQL文を見たことがある人には見慣れたメソッド名ばかりですね。
これらのメソッドを使用して、データベースの基本「CRUD操作」をクエリビルダで行うにはどのようにすればいいのでしょうか。
以下のコードで解説していこうと思います。

use Illuminate\Support\Facades\DB;

// Create: データの挿入
$user = DB::table('users')->insert([
    'name' => '伊藤 四郎',
    'email' => 'siro@test.jp',
    'tel' => 090-2222-1111,
    'birthday' => '1937-06-15',
    'address' => '東京'
]);

// Read: データの取得
$users = DB::table('users')
    ->select('address', DB::raw('count(*) as total'))
    ->groupBy('address')
    ->orderBy('total', 'asc')
    ->get();

$user_id = 1;

// Update: データの更新
DB::table('users')->where('id', $user_id)->update([
    'address' => '北海道',
]);

// Delete: データの削除
DB::table('users')->where('id', $user_id)->delete();

まず最初にクエリビルダを使用するのでDBファサードを定義しています。これがないとエラーになるのでクエリビルダを使用するときは必ず書くようにしてください。 DBファサード定義以降がクエリビルダでの「CRUD操作」実装になります。

では早速、「データの挿入」処理から解説していきます。

Create: データの挿入​

insert()メソッドを使用して「データの挿入」を行っています。
usersテーブルに新しくレコードを追加しており、カラム名をキーにした連想配列の形でinsert()メソッドに渡しています。 連想配列のキーがデータベースのカラム名と一致しない場合はSQLエラーになるので、順番等正確に指定する必要があることを覚えておきましょう！

また、複数レコードを挿入したい場合もあると思います。その際は以下のような方法でデータを挿入することも可能です。

データの挿入（複数）
use Illuminate\Support\Facades\DB;

// 新規ユーザ
$users = [
  [
    'name' => '伊藤 四郎',
    'email' => 'siro@test.jp',
    'tel' => 090-2222-1111,
    'birthday' => '1937-06-15',
    'address' => '東京'
  ],
  [
    'name' => '小林 五郎',
    'email' => 'goro@test.jp',
    'tel' => 090-3333-4444,
    'birthday' => '1999-12-31',
    'address' => '北海道'
  ],
];

$user = DB::table('users')->insert($users);

挿入データの件数が多い場合は、データ配列を別で定義して変数を渡すとよいでしょう。
続いて、「データの取得」処理に移りたいと思います。

Read: データの取得​

get()メソッドを使用して「データの取得」を行っています。
基本的に現場でDB::table('users')->get();を書くことはあまりありません。なので実践的な実装例を上げてみました。
行っている処理としては、usersテーブルのaddressごとにレコードをグループ化し、各レコード数をカウント、その結果を合計値の昇順で並び替えを行い取得しています。

クエリを実行した結果は以下になります。


dd($users);

/*
Collection {#418 ▼
  #items: array:2 [▼
    0 => {#414 ▼
      +"address": "沖縄"
      +"total": 1
    }
    1 => {#415 ▼
      +"address": "東京"
      +"total": 3
    }
  ]
}
*/

addressカラムごとにデータが分かれていて、レコード数もカウントされていることが分かりますね。
取得したデータは確認できましたが、具体的にはどのような処理を行っているのでしょうか。
このクエリ実行にはいくつかのポイントがありますので、順に確認していこうと思います。

ポイント1：「SQL」で直書きしている​

まず１つ目は、select()メソッドの記述です。DB::raw('count(*) as total')の部分は「SQL」を直で書いています。
クエリビルダにはcount()メソッドがあります。ですがそれをここでは使用していません。
この理由は、select()メソッド内でcount()メソッドが使用できないためです。
select()メソッド外でcount()メソッドを使用するとクエリ全体の結果としてレコード数を返すのでうまくいきません。
なのでここは「SQL」を直で書くことで必要なデータをカウントするようにしています。

ポイント2：groupBy()メソッドとorderBy()メソッドの組み合わせ​

２つ目はgroupBy()メソッドとorderBy()メソッドの組み合わせです。
具体的には並び替えを行うカラム指定が重要ポイントになります。
グループ化した後に並び替えをする場合、並び替えの基準になるカラムがグループ化したカラムか、あるいは集計関数（count(), sum()など）を適用したカラムである必要があります。
これはgroupBy()メソッドを使用すると、指定したカラムの値が同じレコードを一つのグループとしてまとまるため、元のテーブルカラムが存在するとは限らないからです。
今回はselect()メソッドで指定しているtotalを並び替えの基準にしているのでエラーにはなりません。
これをもし->orderBy('id', 'asc')などにするとidが存在しないためエラーになってしまいますので気を付けましょう。

「データの取得」処理は以上になります。続いてが「CRUD操作」の最後「データの更新・削除」処理を確認していきましょう。

Update: データの更新 ／ Delete: データの削除​

update()メソッドを使用して「データの更新」、delete()メソッドを使用して「データの削除」を行っています。
両メソッドは、where()メソッドなどでレコードを絞り込まずに実行すると全てのレコードが対象になる可能性があるので注意が必要です。
where()メソッドで絞り込みをする際にあいまいな条件指定は危険です。delete()メソッドは「物理削除」なのでレコードが削除されます。
update()メソッドも値を変更するので実行完了後、元の値には戻せません。
更新や削除はデータの変更を加える操作なので特に慎重に行うように心がけましょう。

クエリビルダの基本的なデータ操作については以上になります。理解できましたでしょうか。
使用する上でのチェックポイントも一緒に紹介してきましたが、上記では紹介できなかった初心者向けの注意点についても少し紹介したいと思います。

注意点​

先ほど「データの取得」でget()メソッドを使用しましたが、こちらで取得したデータの値を使用したい場合の注意点があります。

get()で取得したレコードの値について​

first()メソッドで取得したレコードは、$user->idで値が取れます。
一方、get()メソッドで取得したレコードは、$users->idで値を取ることはできません。
理由は「データの取得」の出力結果を見ればエラーになる理由に気が付くと思います。


dd($users);

/*
Collection {#418 ▼
  #items: array:2 [▼
    0 => {#414 ▼
      +"address": "沖縄"
      +"total": 1
    }
    1 => {#415 ▼
      +"address": "東京"
      +"total": 3
    }
  ]
}
*/

このデータでは「id」が存在しないため、「address」を取りたいとします。
取得したCollection内は配列になっており、直接addressプロパティにアクセスすることはできません。
たとえ取得したCollectionが１件の場合でもエラーになります。この場合は$users->first()->addressとするようにしましょう。

次にデータの更新処理についてですが、上記ではinsert()・update()・delete()を紹介しました。
ですがこのメソッドをそれぞれ使用して更新処理を行うのはパフォーマンスがよくありません。
どうしてよくないのでしょうか。

データ更新処理について​

データ更新する際に考えられる方法は一般的に以下になるかと思います。

  • deleteしてからinsertし直す
  • updateOrInsert()／upsert()メソッドを使用する

１つ目の「deleteしてからinsertし直す」ですがこれは更新処理として良くない実装方法です。どこが良くないのでしょうか。

deleteしてからinsertし直す​

delete/insert
// ユーザーID
$user_id = 1;

// ユーザーデータ
$user_data = [
    'id' => $user_id,
    'name' => 'New User',
    'email' => 'newuser@example.com',
    'tel' => 090-1111-0000,
    'birthday' => 1936-10-04
];

// 既存のユーザーを削除
DB::table('users')->where('id', $user_id)->delete();

// 新しいユーザーとして挿入
DB::table('users')->insert($user_data);

この実装では、delete()メソッドとinsert()メソッドが別々に操作されます。
そのためアトミック性（部分的に実行されることがない状態）を保証してくれません。
削除は成功したが更新で失敗した場合にデータはなくなってしまいます。
削除と挿入の間に他のトランザクションが介入する可能性もあり、データの整合性が保たれない恐れがあります。 これらの点から、パフォーマンス的に良くないことが分かります。

では次の「updateOrInsert()／upsert()メソッドを使用する」実装方法についてはどうでしょうか。

updateOrInsert()／upsert()メソッドを使用する​

どちらもレコードを検索してそのレコードが存在する場合は更新、存在しない場合は挿入するのでほとんど同じように感じます。
実際に別々のメソッドとして存在するので何が違うのか確認してみましょう。

updateOrInsert
// ユーザーID
$user_id = 1;

// 対象のデータ条件
$conditions = ['id' => $user_id];

// 更新または挿入するデータ
$values = [
    'name' => 'New User',
    'email' => 'newuser@example.com',
    'password' => bcrypt('password'),
];

// データを更新または挿入
DB::table('users')->updateOrInsert($conditions, $values);

updateOrInsertは存在確認と挿入処理を別々に行うため２回データベースにアクセスする必要があります。

upsert
// ユーザーデータ
$user_data = [
    'id' => $user_id,
    'name' => 'New User',
    'email' => 'newuser@example.com',
    'tel' => '090-1111-0000',
    'birthday' => '1936-10-04'
];

// データを更新または挿入
DB::table('users')->upsert($user_data, ['id'], ['name', 'email', 'tel', 'birthday']);

upsertは１度のアクセスで済むので効率的かつ複数のレコードを一度に処理するため、大量のデータ更新には特に有効な実装となります。

このことから一般的にupsertの方がパフォーマンスが良いとされています。
ただし実際に使用する際は、データベースシステムでupsertがサポートされているかなどを確認する必要があり、
両方のメソッドを試しベンチマークを取ることをお勧めします。

ここまでで、クエリビルダについてや使用方法とそれに伴う注意点などは紹介しました。
ただ実際にデータベースを操作するにあたり、最も重要になってくるのは「動作確認」だと考えます。

クエリログを確認する​

取得した値の確認だけでは、動作確認としては不十分です。動作確認時に必ずクエリは確認しましょう！
実行クエリが正しいものかを確認することが、パフォーマンスの向上につながります。

実行クエリの確認で知っておくべきメソッドについて紹介していきます。

ヒント

Laravel 10.x系から全てのパラメータがバインドされた状態でクエリを取得できます。

実行されたクエリの確認には以下の方法を使用します。

実行クエリログ確認​

DB::enableQueryLog();

// ... perform queries

$logs = DB::getRawQueryLog();

dd($logs);
/*
[
  [
    "raw_query" => "select * from "users" where "id" in (1, 2, 3)"
    "time" => 0.86
  ]
]
*/

enableQueryLog()： クエリログを有効にする
getRawQueryLog()： バインドされたパラメータを含むクエリログを取得する

クエリ実行回数や実行までにかかった時間が視覚的に分かるため、パフォーマンス分析やデバッグにとても役立ちます。
ただしこの方法では、ログを有効にしてから取得までにクエリが実行されていないと出力は空になります。
不要なデータベースアクセスの確認ができる点ではよいですが、デバッグには実行前のクエリも確認が必要になると思います。

実行されたクエリの実行については上記で確認できることが分かりました。未実行の場合も確認すると思います。
その場合は以下のメソッドを使用します。

未実行のクエリログ確認​

ddRawSql($query); 　  // SQL string output via dd()

作成されたクエリを文字列として取得し、直接出力するためのメソッドです。
出力後のプログラム（ddRawSql以降のコード）は停止するのが特徴です。

dumpRawSql($query);   // SQL string output via dump()

作成されたクエリを文字列として取得し、直接出力するためのメソッドです。
上記のddRawSql()メソッドとは異なり、出力後のプログラム（dumpRawSql以降のコード）も実行されます。

$query->toRawSql(); 　// raw sql string

作成されたクエリを文字列として取得するためのメソッドです。
取得するだけなので出力はされません。

危険

これらのメソッドはデバッグやテスト環境でのみ使用し、本番環境では使用しないように注意してください。

まとめ​

「クエリビルダ」の記事は以上になります。 この記事では、「クエリビルダ」、「Eloquent」、「SQL」3つの実装方法について説明しています。これらを適切に選択したり、組み合わせたりすることが重要です。
プロジェクトに応じて、さまざまな実装方法を検討してみてください。
また「クエリビルダ」も「Eloquent」もどちらも「SQL」が基盤としています。なので「SQL」の知識がなければ、品質の良いものをつくることは難しいです。
そのことを念頭に置きつつ、開発を進めていきましょう。

【備忘録】Laravel 9.x系以前でのパラメータ付きクエリログ確認方法​

$query = User::query()->whereIn('id', [1, 2, 3]);

$sql = $query->toSql();
dd($sql);
// "select * from "users" where "id" in (?, ?, ?)"

$sql = preg_replace_array('/\?/', array_map(function ($n) { return "'".$n."'"; }, $query->getBindings()), $query->toSql());
dd($sql);
// "select * from "users" where "id" in (1, 2, 3)"

Footnotes​

  1. 取得処理(get(), first(), find()等)が実際に必要となるまでその実行を遅らせること ↩ ↩2 ↩3 ↩4