DBデータ操作(DBファサード、クエリビルダ、Eloquent)

Tag:

DBデータ操作としてSQLを作成する際に、私がよく利用するメソッド一覧です。覚書として残してます。

DBファサード

メソッド 概要・例
DB::select()
$users = DB::select('select * from users');
DB::raw() SQL関数やCASE文など利用したいときに使う
DB::transaction()
DB::transaction(function () {
    // データベース処理
});

システムコマンドの実行

例えばMySQLのSHOWコマンドを実行するには、以下のようにします。

// DB内のテーブル一覧表示
$tables = DB::select('SHOW TABLES');       
foreach ($tables as $table) {
    echo $table->{'Tables_in_' . env('DB_DATABASE')};
}

// usersテーブル内のカラム一覧表示
$columns = DB::select('SHOW COLUMNS FROM users');    
foreach ($columns as $column) {
    echo $column->Field;
}

クエリビルダ

メソッド
select() カラム指定

CASE文など利用したい場合、DBファサードのrawメソッドを利用。

select(DB::raw('sum(case when user.activated_at is not null then 1 else 0 end) as actcnt'))
where()
orWhere()
条件指定

->where('id', '=', 100)

複雑な条件はクロージャで指定。

// 例. (条件A or (条件B and 条件c)) 
->where(条件A)
->orWhere(function ($query) {
    $query->where(条件B)
          ->where(条件C);
})
whereNull()
whereNotNull()
Null判定
whereIn()
whereNotIn
In判定

whereIn('old', [20,30,40])
groupBy() GROUPBY句
having() HAVING句
orderBy() ORDERBY句

昇順の場合

->orderBy('カラム', 'desc')
take() LIMIT値
union() UNION
leftJoin() LEFT JOIN

DB::table('users')
    ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
    ->get();

サブクエリとの結合は以下のようにします。

DB::table('users')
    ->leftJoin(DB::raw('(SELECT user_id, COUNT(user_id) as post_cnt '
                     . ' FROM `posts` '
                     . ' GROUP BY user_id) TotalPost'), function($join) {
                $join->on('users.id', '=', 'TotalPost.user_id');
            })
    ->get();
insert() データ挿入
update() データ更新
delete() データ削除
truncate() 指定テーブルの全データ削除
get() すべての結果取得。Collectionのインスタンスとして返却。countメソッドやisEmptyメソッドなどCollectionクラスのメソッドが使える。
first() 1行だけ取得
集計メソッド 集計結果を取得
count、max、min、avg、sum

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

$users = DB::table('users')
            ->whereNotNull('activated_at')
            ->count();

動的に条件を設定したい場合

例えば、リクエストパラメーターが設定されている時だけ条件を設定するには、以下のようにwhenメソッドを利用します。

$name = $request->input('name');
$old = $request->input('old');

$users = DB::table('users')
                ->when($name, function ($query) use ($name) {
                    return $query->where('name', $name);
                })
                ->when($old, function ($query) use ($old) {
                    return $query->where('old', $old);
                })
                ->get();

whenメソッドの第1引数がfalseの場合、クロージャーを実行しません。

Eloquent ORM

メソッド
find()
$user = App\User::find(1);
save()
// データ挿入
$user = new User();
$user->name = 'yamada';
$user->save();

// データ更新
$user = User::find(5);
$user->old = 22;
$user->save;
create()
User::create([
    'name' => 'yamada',
    'old' => 22
]);
※Mass Assignmentに注意
$fillableプロパティ、または$guardedプロパティで更新を許可するプロパティを設定
※便利メソッド
findOrFail
モデルの取得。存在しない場合、404HTTPレスポンスを返す。

firstOrCreate
モデルの取得。存在しなければレコードに挿入して、インスタンス化

firstOrNew
モデルの取得。存在しなければインスタンス化

updateOrCreate
モデルの更新。存在しなければレコードに挿入して、インスタンス化

スコープの利用

Eloquentでは、よく利用する制約を一箇所にまとめるためのスコープ機能を提供しています。

グローバルスコープ
指定したモデルの全クエリに対して、制約を付け加える

方法1
1. グローバルスコープを作成
例). app/Scopes/XxxScopeを作り、applyメソッド内にグローバルスコープを記述

2. グローバルスコープの適用
グローバルスコープを適用したいモデルのbootメソッドで、グローバルスコープを適用

方法2
1. グローバルスコープを適用したいモデルのbootメソッドで、クロージャでグローバルスコープを記述

ローカルスコープ
指定したモデルの特定クエリに対して、制約を付け加える。

モデル内でscopeプレフィックスをつけたメソッドを定義。
呼び出すときは、scopeプレフィックスはいらない。

イベントをフック

bootメソッド内で任意のイベント(creating、created、updating、updated、saving、saved、deleting、deleted、restoring、restored)をフックした処理を記述できます。

ミューテタでできること

getXxxXxxAttributeで特定カラムの値を加工して取得
setXxxXxxAttributeで特定カラムの値を加工してDBに登録
datesプロパティでCarbonインスタンスへ変換するカラム指定
castsプロパティでキャストするカラム指定

スポンサーリンク