Laravelのクエリビルダ記法まとめ（QueryBuilder/DB Facade）

$data = $users->get();

// ループで1件ずつ取得
foreach ($data as $d) {
    echo $d->name;
}

// 結果データそのものから直接取得
echo $data[0]->name;

$data = $users->first();

echo $data->name;

$data = $users->value('email');

echo $data; // test01@test.com

$data = $users->pluck('email');

Array
(
    [0] => test01@test.com
    [1] => test03@test.com
    [2] => test04@test.com
    [3] => test05@test.com
    [4] => test06@test.com
    [5] => test07@test.com
    [6] => test08@test.com
    [7] => test09@test.com
    [8] => test10@test.com
)

// ループで1件ずつ取得
foreach ($data as $d) {
    echo $d;
}

// 結果データそのものからアクセス
echo $data[0];

$data = $users->pluck('email', 'name');

Array
(
    [user01] => test01@test.com
    [user02] => test02@test.com
    [user03] => test03@test.com
    [user04] => test04@test.com
    [user05] => test05@test.com
    [user06] => test06@test.com
    [user07] => test07@test.com
    [user08] => test08@test.com
    [user09] => test09@test.com
    [user10] => test10@test.com
)

$data = $users->exists();

if ($data) {
    // レコードは存在する
} elseif (!$data) {
    // レコードは存在しない
}

$data = $users->doesntExist();

if ($data) {
    // レコードは存在しない
} elseif (!$data) {
    // レコードは存在する
}

$data = $users->toSql();

echo $data;  // => select * from `users`

$data = $users->count();

echo $data;  // => 10（件）

$data = $users->max('id');

echo $data;  // => 10

$data = $users->min('id');

echo $data;  // => 1

$data = $users->avg('id');

echo $data;  // => 5.5000

$data = $users->sum('id');

echo $data;  // => 55

$data = $users
    ->join('role', 'users.role', '=', 'role.role_id')
    ->join('message', 'users.id', '=', 'message.user_id')
    ->get();

$data = $users
    ->leftJoin('message', 'users.id', '=', 'message.user_id')
    ->get();

$data = $users->crossJoin('role')->get();

$data = $users
    ->join('contacts', function ($join) {
        $join->on('users.id', '=', 'contacts.user_id')
             ->orOn(...);
    })
    ->get();

$data = $users
    ->join('contacts', function ($join) {
        $join->on('users.id', '=', 'contacts.user_id')
             ->where('contacts.user_id', '>', 5);
    })
    ->get();

// サブクエリ
$roles = DB::table('mst_role')->select('id', 'name')->where('deleted_at', null);

// サブクエリをJOINします
$users = DB::table('users')
            ->select('users.id', 'users.name', 'roles.name AS role')
            ->joinSub($roles, 'roles', function ($join) {
                $join->on('users.role', '=', 'roles.id');
            })->get();

print_r($users);
// [0] => stdClass Object
//        (
//            [id] => 1
//            [name] => user01
//            [role] => システム管理者
//        )
//
// [1] => stdClass Object
//        (
//            [id] => 2
//            [name] => user02
//            [role] => 管理者
//        )
//
// [2] => stdClass Object
//        (
//             [id] => 3
//             [name] => user03
//             [role] => ゲスト
//        )

$data = $users->where('id', '>', 5)->get();

// id=5 の場合
$data = $users->where('id', 5)->get();

$data = $users->where('email', 'like', '%test.com')->get();

$data = $users->where([
    ['role', '>', 5],
    ['id', '>', 6]
])->get();

$data = $users
    ->where('role', '>', 5)
    ->orWhere('role', '>', 8)
    ->get();

$data = $users->whereBetween('id', [4, 6])->get();

$data = $users->whereNotBetween('id', [4, 6])->get();

$data = $users->whereMonth('updated_at', 2)->get();

$data = $users->whereDay('updated_at', 25)->get();

$data = $users->whereYear('updated_at', 2018)->get();

$data = $users->whereTime('updated_at', '<', '12:00')->get();

$data = $users
    ->whereJsonContains('options->language', 'ja')
    ->get();

// 複数の値を指定する
$data = $users
    ->whereJsonContains('options->language', ['ja', 'en'])
    ->get();

$data = $users
    ->whereJsonLength('team->user', 5)
    ->get();

$data = $users
    ->whereJsonLength('team->user', '>', 5)
    ->get();

[{
  "team": {
    "user": [
      {"name": "user01"},
      {"name": "user02"},
      {"name": "user03"},
      {"name": "user04"},
      {"name": "user05"},
    ]
  }
 },{
  "team": {
    "user": [
      {"name": "user001"},
      {"name": "user002"},
      {"name": "user003"},
    ]
  }
}]

$data = $users->inRandomOrder()->get();

$data = $users->limit(3)->get();

$data = $users
    ->offset(2)
    ->limit(3)
    ->get();

$data = $users
    ->insertGetId(
        [
            'name' => 'test11',
            'email' => 'test11@test.com',
            'password' => '123456',
            'role' => 5
        ]
    );

$data = $users
    ->where('id', 1)
    ->update(['options->enabled' => true]);

$data = $users->where('id', 1)->increment('count');
$data = $users->where('id', 1)->increment('count', 3);

$data = $users->where('id', 1)->decrement('count');
$data = $users->where('id', 1)->decrement('count', 3);

$data = $users->where('id', 1)->increment('count', 3, ['name' => 'test1-2']);

$users->truncate();

$data = $users->where('id', 1)->sharedLock()->get();

$data = $users->where('id', 1)->lockForUpdate()->get();