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

2016/09/092022/11/19

DBデータ操作としてSQLを作成する際に利用しそうなメソッドや、Eloquentの操作方法について取り上げます。

DBファサード

DB::select()

SELECTクエリを実行できます。

$users = DB::select('select * from users where active = ?', [1000]);

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;
}

DB::raw()

rawメソッドの場合、エスケープなしのSQLを実行します。
SQL関数やCASE文など利用したいときに利用します。

DB::transaction()

トランザクション処理を実行したいときに利用します。

DB::transaction(function () {
    // トランザクション処理
});

クエリビルダ

select()
( カラム指定 )

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

DB::table('users')->select(DB::raw('sum(case when users.activated_at is not null then 1 else 0 end) as actcnt'))

where(), orWhere()
( 条件指定 )

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

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

// 例. (条件A or (条件B and 条件c))
$query->where(条件A)
      ->orWhere(function ($query) {
          $query->where(条件B)
                ->where(条件C);
      })

whereNull(), whereNotNull()
( Null判定 )

$query->whereNull('カラム');

whereIn(), whereNotIn
( IN判定 )

$query->whereIn('カラム', [20,30,40])

groupBy()
( GROUPBY句 )

$query->groupBy('カラム')

having()
( HAVING句 )

$query->having('カラム', '>=', 100)

orderBy()
( ORDERBY句 )

降順の場合は以下のように記述します。

$query->orderBy('カラム', 'desc')

latest(), oldest()
( 日付のソート )

デフォルトで、created_atによってソートされます。

take()
( LIMIT値 )

$query->take(10)

union()
( UNION )

$query->union(DB::table('users'))

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	更新
increment	指定カラムの値を増やす
decrement	指定カラムの値を減らす
delete	削除
truncate	指定テーブルの全データ削除

取得

メソッド	概要
get	クエリ結果を全て取得します。 Collectionのインスタンスとして返却します。そのため、`countメソッド`や`isEmptyメソッド`などCollectionクラスのメソッドが使えます。
first	1行だけ取得します。
pluck	カラムを指定して、クエリ結果を全て取得します。
chunk	分割して取得します。

count、max、min、avg、sum
( 集計メソッド )

集計結果を取得します。

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

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

sharedLock()
( 共有ロック )

共有ロックを設定すると、他のトランザクションから共有ロックできますが、排他ロックできないようになります。
つまり、読み取りはできても、変更ができない状態になります。

lockForUpdate
( 排他ロック )

排他ロックを設定すると、他のトランザクションから共有ロック、排他ロック共にできないようになります。
つまり、読み取り、変更共にできない状態になります。

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

例えば、リクエストパラメータが設定されている時だけ条件を設定するには、以下のように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の場合、クロージャーを実行しません。

クエリビルダでの更新時の注意

以下のようにクエリビルダでレコードを更新しても created_at updated_at は更新されません。

DB::table('users')->insert(['name' => 'xxxx']);
DB::table('users')->where('id', 22)->update(['name' => 'xxxx']);

更新されるのは、以下のようにEloquentを利用したときだけです。

App\Models\User::where('id', 22)->update(['name' => 'xxxx']);

Eloquent ORM

find()｜取得

$user = App\User::find(1);

save()｜挿入 or 更新

// データ挿入
$user = new User();
$user->name = 'yamada';
$user->save();
 
// データ更新
$user = User::find(5);
$user->old = 22;
$user->save;

update()｜複数のモデルを更新

App\User::where('active', 1)->update(['note' => 'xxxxxxx']);

create()｜挿入

User::create([
    'name' => 'yamada',
    'old' => 22
]);

Mass Assignmentに注意

createメソッド で複数代入する際は、事前にModelの設定が必要です。

$fillableプロパティ または $guardedプロパティ で更新を許可するプロパティを設定します。

便利メソッド

メソッド	概要
findOrFail	モデルの取得。存在しない場合、404HTTPレスポンスを返す。
firstOrCreate	モデルの取得。存在しなければレコードに挿入して、インスタンス化
firstOrNew	モデルの取得。存在しなければインスタンス化
updateOrCreate	モデルの更新。存在しなければレコードに挿入して、インスタンス化
chunk	分割数を指定して取得
cursor	1行ずつ取得

delete()｜削除

App\User::find(1)->delete();

App\User::where('active', 1)->delete();

destroy()｜キー指定で削除

App\User::destroy([1, 2, 3]);

論理削除

論理削除関連で利用するメソッドです。

メソッド	概要
withTrashed	論理削除済みのレコードも取得
onlyTrashed	論理削除済みのレコードだけを取得
trashed	指定されたモデルインスタンスが論理削除されているか確認
restore	論理削除されていない状態に更新
forceDelete	完全に削除

スコープの利用

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

グローバルスコープ

指定したモデルの全クエリに対して、制約を付け加えることができます。

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

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

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

ローカルスコープ

指定したモデルの特定クエリに対して、制約を付け加えることができます。

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

イベントをフック

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

ミューテタでできること

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

以下、getXxxXxxAttributeの例です。

public function getFormattedBirthdayAttribute()
{
    return $this->birthday->format('Y年m月d日');
}

対応するテストは次のように記述できます。

function can_get_formatted_birthday()
{
    $user = factory(User::class)->make([
        'birthday' => Carbon::parse('2017-01-15 12:30:15'),
    ]);
 
    $this->assertEquals('2017年01月15日', $user->formatted_birthday);
}

リレーションメソッド

下記ページで取り上げてます。

シリアライズ

EloquentをJSONデータに変換する便利なメソッドを持ってます。APIなどで活躍します。

// 配列に変換
return App\User::with('roles')->first()->toArray();
 
// jsonに変換
return App\User::with('roles')->first()->toJson();

「自身のモデル」と「ロード済みリレーションモデル」が変換されます。

JSONに含むデータを指定できます。ブラックリストで定義するなら $hiddenプロパティ を利用し、ホワイトリストで定義するなら $visibleプロパティ を利用します。

$hiddenプロパティ $visibleプロパティ の値を動的に変更するためにメソッドも提供されてます。
(makeHidden makeVisible)

存在判定

取得結果が0件である場合の判定方法は利用するメソッドによって異なります。

find(), first()

戻り値として、Eloquentインスタンス か null が返ります。
なので is_null() を利用して判定します。

get(), all()

戻り値として、Illuminate\Database\Eloquent\Collectionのインスタンス が返ります。
なので、Collectionクラスの isEmpty() を利用して判定します。

よかったらシェアしてね！

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

DBファサード

DB::select()

DB::raw()

DB::transaction()

クエリビルダ

select()( カラム指定 )

where(), orWhere()( 条件指定 )

whereNull(), whereNotNull()( Null判定 )

whereIn(), whereNotIn( IN判定 )

groupBy()( GROUPBY句 )

having()( HAVING句 )

orderBy()( ORDERBY句 )

latest(), oldest()( 日付のソート )

take()( LIMIT値 )

union()( UNION )

leftJoin()( LEFT JOIN )