データベース:クエリビルダ
イントロダクション
データベースクエリビルダはスラスラと書ける(fluent)便利なインターフェイスで、クエリを作成し実行するために使用します。アプリケーションで行われるほとんどのデーターベース操作が可能で、サポートしている全データベースシステムに対し使用できます。
LaravelクエリビルダはアプリケーションをSQLインジェクション攻撃から守るために、PDOパラメーターによるバインディングを使用します。バインドする文字列をクリーンにしてから渡す必要はありません。
結果の取得
全レコードの取得
クエリを書くにはDB
ファサードのtable
メソッドを使います。table
メソッドは指定したテーブルに対するクエリビルダインスタンスを返します。これを使いクエリに制約を加え、最終的な結果を取得するチェーンを繋げます。次に、最終的な結果をget
で取得します。
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;
class UserController extends Controller
{
/**
* アプリケーションの全ユーザーレコード一覧を表示
*
* @return Response
*/
public function index()
{
$users = DB::table('users')->get();
return view('user.index', ['users' => $users]);
}
}
get
メソッドは、PHPのStdClass
オブジェクトのインスタンスを結果として含む、Illuminate\Support\Collection
を返します。各カラムの値は、オブジェクトのプロパティとしてアクセスできます。
foreach ($users as $user) {
echo $user->name;
}
テーブルから1カラム/1レコード取得
データベーステーブルから1レコードのみ取得する必要がある場合は、first
メソッドを使います。このメソッドはStdClass
オブジェクトを返します。
$user = DB::table('users')->where('name', 'John')->first();
echo $user->name;
全カラムは必要ない場合、value
メソッドにより一つの値のみ取得できます。このメソッドはカラムの値を直接返します。
$email = DB::table('users')->where('name', 'John')->value('email');
カラム値をリストで取得
単一カラムの値を値配で取得したい場合はpluck
メソッドを使います。以下の例では役割名(title)を配列で取得しています。
$titles = DB::table('roles')->pluck('title');
foreach ($titles as $title) {
echo $title;
}
取得配列のキーカラムを指定することもできます。
$roles = DB::table('roles')->pluck('title', 'name');
foreach ($roles as $name => $title) {
echo $title;
}
結果の分割
数千のデータベースレコードを扱う場合はchunk
メソッドの使用を考慮してください。このメソッドは一度に小さな「かたまり(chunk)」で結果を取得し、このチャンクは処理のために「クロージャ」に渡されます。このメソッドは数千のレコードを処理するArtisanコマンドを書くときに便利です。users
レコード全体から一度に100レコードずつチャンクを処理する例を見てください。
DB::table('users')->orderBy('id')->chunk(100, function($users) {
foreach ($users as $user) {
//
}
});
クロージャからfalse
を返すとチャンクの処理を中断できます。
DB::table('users')->orderBy('id')->chunk(100, function($users) {
// Process the records...
return false;
});
集計
またクエリビルダはcount
、max
、min
、avg
、sum
など多くの集計メソッドを提供しています。クエリを制約した後にこれらのメソッドを使うことも可能です。
$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');
もちろんこれらのメソッドをクエリを構築するために他の節と組み合わせて使用できます。
$price = DB::table('orders')
->where('finalized', 1)
->avg('price');
Selects
Specifying A Select Clause
Of course, you may not always want to select all columns from a
database table. Using the select
method, you can specify a
custom select
clause for the query:
$users = DB::table('users')->select('name', 'email as user_email')->get();
The distinct
method allows you to force the query to
return distinct results:
$users = DB::table('users')->distinct()->get();
If you already have a query builder instance and you wish to add a
column to its existing select clause, you may use the
addSelect
method:
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();
Raw Expressions
Sometimes you may need to use a raw expression in a query. These
expressions will be injected into the query as strings, so be careful
not to create any SQL injection points! To create a raw expression, you
may use the DB::raw
method:
$users = DB::table('users')
->select(DB::raw('count(*) as user_count, status'))
->where('status', '<>', 1)
->groupBy('status')
->get();
Joins
Inner Join Clause
The query builder may also be used to write join statements. To
perform a basic "inner join", you may use the join
method
on a query builder instance. The first argument passed to the
join
method is the name of the table you need to join to,
while the remaining arguments specify the column constraints for the
join. Of course, as you can see, you can join to multiple tables in a
single query:
$users = DB::table('users')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.*', 'contacts.phone', 'orders.price')
->get();
Left Join Clause
If you would like to perform a "left join" instead of an "inner
join", use the leftJoin
method. The leftJoin
method has the same signature as the join
method:
$users = DB::table('users')
->leftJoin('posts', 'users.id', '=', 'posts.user_id')
->get();
Cross Join Clause
To perform a "cross join" use the crossJoin
method with
the name of the table you wish to cross join to. Cross joins generate a
cartesian product between the first table and the joined table:
$users = DB::table('sizes')
->crossJoin('colours')
->get();
Advanced Join Clauses
You may also specify more advanced join clauses. To get started, pass
a Closure
as the second argument into the join
method. The Closure
will receive a JoinClause
object which allows you to specify constraints on the join
clause:
DB::table('users')
->join('contacts', function ($join) {
$join->on('users.id', '=', 'contacts.user_id')->orOn(...);
})
->get();
If you would like to use a "where" style clause on your joins, you
may use the where
and orWhere
methods on a
join. Instead of comparing two columns, these methods will compare the
column against a value:
DB::table('users')
->join('contacts', function ($join) {
$join->on('users.id', '=', 'contacts.user_id')
->where('contacts.user_id', '>', 5);
})
->get();
Unions
The query builder also provides a quick way to "union" two queries
together. For example, you may create an initial query and use the
union
method to union it with a second query:
$first = DB::table('users')
->whereNull('first_name');
$users = DB::table('users')
->whereNull('last_name')
->union($first)
->get();
Tip!! The
unionAll
method is also available and has the same method signature asunion
.
Where Clauses
Simple Where Clauses
You may use the where
method on a query builder instance
to add where
clauses to the query. The most basic call to
where
requires three arguments. The first argument is the
name of the column. The second argument is an operator, which can be any
of the database's supported operators. Finally, the third argument is
the value to evaluate against the column.
For example, here is a query that verifies the value of the "votes" column is equal to 100:
$users = DB::table('users')->where('votes', '=', 100)->get();
For convenience, if you simply want to verify that a column is equal
to a given value, you may pass the value directly as the second argument
to the where
method:
$users = DB::table('users')->where('votes', 100)->get();
Of course, you may use a variety of other operators when writing a
where
clause:
$users = DB::table('users')
->where('votes', '>=', 100)
->get();
$users = DB::table('users')
->where('votes', '<>', 100)
->get();
$users = DB::table('users')
->where('name', 'like', 'T%')
->get();
You may also pass an array of conditions to the where
function:
$users = DB::table('users')->where([
['status', '=', '1'],
['subscribed', '<>', '1'],
])->get();
Or Statements
You may chain where constraints together as well as add
or
clauses to the query. The orWhere
method
accepts the same arguments as the where
method:
$users = DB::table('users')
->where('votes', '>', 100)
->orWhere('name', 'John')
->get();
その他のWHERE節
whereBetween
whereBetween
メソッドはカラムの値が2つの値の間である条件を加えます。
$users = DB::table('users')
->whereBetween('votes', [1, 100])->get();
whereNotBetween
whereNotBetween
メソッドは、カラムの値が2つの値の間ではない条件を加えます。
$users = DB::table('users')
->whereNotBetween('votes', [1, 100])
->get();
whereIn / whereNotIn
whereIn
メソッドは指定した配列の中にカラムの値が含まれている条件を加えます。
$users = DB::table('users')
->whereIn('id', [1, 2, 3])
->get();
whereNotIn
メソッドはカラムの値が指定した配列の中に含まれていない条件を加えます。
$users = DB::table('users')
->whereNotIn('id', [1, 2, 3])
->get();
whereNull / whereNotNull
whereNull
メソッドは指定したカラムの値がNULL
である条件を加えます。
$users = DB::table('users')
->whereNull('updated_at')
->get();
whereNotBetween
メソッドは指定したカラムの値がNULL
でない条件を加えます。
$users = DB::table('users')
->whereNotNull('updated_at')
->get();
whereDate / whereMonth / whereDay / whereYear
whereDate
メソッドはカラム値を日付と比較する時に使用します。
$users = DB::table('users')
->whereDate('created_at', '2016-10-10')
->get();
whereMonth
メソッドはカラム値と、ある年の指定した月とを比較します。
$users = DB::table('users')
->whereMonth('created_at', '10')
->get();
whereDay
メソッドはカラム値と、ある月の指定した日とを比べます。
$users = DB::table('users')
->whereDay('created_at', '10')
->get();
whereYear
メソッドはカラム値と、指定した年とを比べます。
$users = DB::table('users')
->whereYear('created_at', '2016')
->get();
whereColumn
whereColumn
メソッドは2つのカラムが同値である確認をするのに使います。
$users = DB::table('users')
->whereColumn('first_name', 'last_name')
->get();
メソッドに比較演算子を追加指定することもできます。
$users = DB::table('users')
->whereColumn('updated_at', '>', 'created_at')
->get();
whereColumn
へ配列により複数の条件を渡すこともできます。各条件はand
オペレータでつなげられます。
$users = DB::table('users')
->whereColumn([
['first_name', '=', 'last_name'],
['updated_at', '>', 'created_at']
])->get();
パラメータのグループ分け
時には、"WHERE EXISTS"節やグループにまとめたパラーメーターのネストのような、上級のWHERE節を作成する必要が起きます。Laravelクエリビルダはこれらもうまく処理できます。手始めに、カッコで制約をまとめる例を見てください。
DB::table('users')
->where('name', '=', 'John')
->orWhere(function ($query) {
$query->where('votes', '>', 100)
->where('title', '<>', 'Admin');
})
->get();
ご覧の通り、orWhere
メソッドに渡している「クロージャ」が、クエリビルダのグルーピングを指示しています。生成するSQLの括弧内で展開される制約を指定できるように、「クロージャ」はクエリビルダのインスタンスを受け取ります。
select * from users where name = 'John' or (votes > 100 and title <> 'Admin')
Where Exists節
whereExists
メソッドはWHERE EXISTS
のSQLを書けるように用意しています。whereExists
メソッドは引数に「クロージャー」を取り、"EXISTS"節の中に置かれるクエリーを定義するためのクエリービルダーを受け取ります。
DB::table('users')
->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('orders')
->whereRaw('orders.user_id = users.id');
})
->get();
上のクエリは以下のSQLを生成します。
select * from users
where exists (
select 1 from orders where orders.user_id = users.id
)
JSON WHERE節
Laravelはデータベース上のJSONタイプをサポートするカラムに対するクエリに対応しています。現在、MySQL5.7とPostgresに対応しています。JSONカラムをクエリーするには->
オペレータを使ってください。
$users = DB::table('users')
->where('options->language', 'en')
->get();
$users = DB::table('users')
->where('preferences->dining->meal', 'salad')
->get();
順序、グループ分け、制限、オフセット
orderBy
orderBy
メソッドは指定したカラムでクエリ結果をソートします。orderBy
メソッドの最初の引数はソート対象のカラムで、第2引数はソートの降順(asc
)と昇順(desc
)をコントロールします。
$users = DB::table('users')
->orderBy('name', 'desc')
->get();
inRandomOrder
inRandomOrder
メソッドはクエリ結果をランダムな順番にしたい時に使用します。たとえば、以下のコードはランダムにユーザを一人取得します。
$randomUser = DB::table('users')
->inRandomOrder()
->first();
groupBy / having / havingRaw
groupBy
とhaving
メソッドはクエリ結果をグループにまとめるために使用します。having
メソッドはwhere
メソッドと似た使い方です。
$users = DB::table('users')
->groupBy('account_id')
->having('account_id', '>', 100)
->get();
havingRaw
メソッドはhaveing
節の値としてSQL文字列をそのまま指定するために使用します。たとえば2,500ドルより多く売り上げている部門(department)を全部見つけましょう。
$users = DB::table('orders')
->select('department', DB::raw('SUM(price) as total_sales'))
->groupBy('department')
->havingRaw('SUM(price) > 2500')
->get();
skip / take
クエリから限られた(LIMIT
)数のレコードを受け取ったり、結果から指定した件数を飛ばしたりするには、skip
とtake
メソッドを使います。
$users = DB::table('users')->skip(10)->take(5)->get();
別の方法として、limit
とoffset
メソッドも使用できます。
$users = DB::table('users')
->offset(10)
->limit(5)
->get();
条件節
ある条件がtrueの場合の時のみ、クエリへ特定の文を適用したい場合があります。例えば特定の入力値がリクエストに含まれている場合に、where
文を適用する場合です。when
メソッドで実現できます。
$role = $request->input('role');
$users = DB::table('users')
->when($role, function ($query) use ($role) {
return $query->where('role_id', $role);
})
->get();
when
メソッドは、第1引数がtrue
の時のみ、指定されたクロージャを実行します。最初の引数がfalse
の場合、クロージャを実行しません。
when
メソッドの第3引数に別のクロージャを渡せます。このクロージャは、最初の引数の評価がfalse
であると実行されます。この機能をどう使うかを確認するため、クエリのデフォルトソートを設定してみましょう。
$sortBy = null;
$users = DB::table('users')
->when($sortBy, function ($query) use ($sortBy) {
return $query->orderBy($sortBy);
}, function ($query) {
return $query->orderBy('name');
})
->get();
INSERT
クエリビルダは、データベーステーブルにレコードを挿入するためのinsert
メソッドを提供しています。insert
メソッドは挿入するカラム名と値の配列を引数に取ります。
DB::table('users')->insert(
['email' => 'john@example.com', 'votes' => 0]
);
配列の配列をinsert
に渡して呼び出すことで、テーブルにたくさんのレコードを一度にまとめて挿入できます。
DB::table('users')->insert([
['email' => 'taylor@example.com', 'votes' => 0],
['email' => 'dayle@example.com', 'votes' => 0]
]);
自動増分ID
テーブルが自動増分IDを持っている場合、insertGetId
メソッドを使うとレコードを挿入し、そのレコードのIDを返してくれます。
$id = DB::table('users')->insertGetId(
['email' => 'john@example.com', 'votes' => 0]
);
Note: PostgreSQLでinsertGetIdメソッドを使う場合、自動増分カラム名は
id
である必要があります。他の「シーケンス」からIDを取得したい場合は、insertGetId
メソッドの第2引数にシーケンス名を指定してください。
UPDATE
もちろん、データベースへレコードを挿入するだけでなく、存在しているレコードをupdate
メソッドで更新することもできます。update
メソッドはinsert
メソッドと同様に、更新対象のカラムのカラム名と値の配列を引数に受け取ります。更新するクエリをwhere
節を使って制約することもできます。
DB::table('users')
->where('id', 1)
->update(['votes' => 1]);
JSONカラムの更新
JSONカラムを更新する場合、JSONオブジェクトの中の適切なキーへアクセスするために、->
記法を使ってください。JSONカラムをサポートしているデータベースでのみ、このオペレータをサポートしています。
DB::table('users')
->where('id', 1)
->update(['options->enabled' => true]);
増減分
クエリビルダは指定したカラムの値を増やしたり、減らしたりするのに便利なメソッドも用意しています。これはシンプルな短縮記法で、update
分で書くのに比べるとより記述的であり、簡潔なインターフェイスを提供しています。
両方のメソッドともに、最小1つ、更新したいカラムを引数に取ります。オプションの第2引数はそのカラムの増減値を指定します。
DB::table('users')->increment('votes');
DB::table('users')->increment('votes', 5);
DB::table('users')->decrement('votes');
DB::table('users')->decrement('votes', 5);
さらに増減操作と一緒に更新する追加のカラムを指定することもできます。
DB::table('users')->increment('votes', 1, ['name' => 'John']);
DELETE
クエリビルダはdelete
メソッドで、テーブルからレコードを削除するためにも使用できます。
delete
メソッドを呼び出す前にwhere
節を追加し、delete
文を制約することもできます。
DB::table('users')->delete();
DB::table('users')->where('votes', '>', 100)->delete();
全レコードを削除し、自動増分のIDを0にリセットするためにテーブルをTRUNCATEしたい場合は、truncate
メソッドを使います。
DB::table('users')->truncate();
悲観的ロック
クエリビルダは、SELECT
文で「悲観的ロック」を行うための機能をいくつか持っています。SELECT文を実行する間「共有ロック」をかけたい場合は、sharedLock
メソッドをクエリに指定して下さい。共有ロックはトランザクションがコミットされるまで、SELECTしている行が更新されることを防ぎます。
DB::table('users')->where('votes', '>', 100)->sharedLock()->get();
もしくはlockForUpdate
メソッドが使えます。占有ロックをかけることで、レコードを更新したりSELECTするために他の共有ロックが行われるのを防ぎます。
DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();