laravel 5.5 -- Query Builder

读

DB::table('users')->get();                             // 返回包含对象的集合
DB::table('users')->distinct()->get();
DB::table('users')->select('id', 'age as user_age')->get();
DB::table('users')->where('name', 'John')->first();    // 返回对象
DB::table('users')->where('id', 17)->value('age');     // 返回 17
DB::table('users')->orderBy('id')->value('age');       // 返回 id 最小的 age
DB::table('users')->pluck('age');                      // 返回包含字段值的集合
DB::table('users')->pluck('age', 'id');                // 返回关联集合 id => age，最多 2 个参数
DB::table('users')->count();                           // 返回数字
DB::table('users')->max('id');                         // 返回数字或 null
DB::table('users')->where('class_id', '1')->average('age'); // 返回四位小数的平均值或 null

DB::table('users')->orderBy('id')->chunk(100, function ($users) {  // 取每 100 个一组
    foreach ($users as $user) {
        // ...
        // return false;                                           // 随时可以退出
    }
});                                                     

$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();

$users = DB::table('users')
                ->select(DB::raw('count(*) as user_count, status'))
                ->where('status', '<>', 1)
                ->groupBy('status')
                ->get();

// selectRaw 可以传一个参数绑定

DB::table('orders')
                ->selectRaw('price * ? as price_with_tax', [1.0825])
                ->get();

$orders = DB::table('orders')
                ->whereRaw('price > IF(state = "TX", ?, 100)', [200])
                ->get();

$orders = DB::table('orders')
                ->select('department', DB::raw('SUM(price) as total_sales'))
                ->groupBy('department')
                ->havingRaw('SUM(price) > 2500')
                ->get();

$orders = DB::table('orders')
                ->orderByRaw('updated_at - created_at DESC')      // - 只能用于 timestamp 类型        
                ->get();

# inner join
$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
$users = DB::table('users')
            ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
            ->get();

# cross join
$users = DB::table('sizes')
            ->crossJoin('colours')
            ->get();

# 高级 join
DB::table('users')
        ->join('contacts', function ($join) {
            $join->on('users.id', '=', 'contacts.user_id')->orOn(...);
        })
        ->get();

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

# union
$first = DB::table('users')
            ->whereNull('first_name');

$users = DB::table('users')
            ->whereNull('last_name')
            ->union($first)
            ->get();

whereNull('last_name')
where('votes', '=', 100)
where('votes', 100)
where('votes', '>=', 100)
where('votes', '<>', 100)
where('name', 'like', 'T%')
where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])
where('votes', '>', 100)->orWhere('name', 'John')
whereBetween('votes', [1, 100])
whereNotBetween('votes', [1, 100])
whereIn('id', [1, 2, 3])
whereNotIn('id', [1, 2, 3])
whereNotNull('updated_at')
whereDate('created_at', '2016-12-31')
whereMonth('created_at', '12')
whereDay('created_at', '31')
whereYear('created_at', '2016')
whereColumn('first_name', 'last_name')        // 判断两个字段 相等
whereColumn('updated_at', '>', 'created_at')
whereColumn([
    ['first_name', '=', 'last_name'],
    ['updated_at', '>', 'created_at']
])
where('name', '=', 'John')
->orWhere(function ($query) {
    $query->where('votes', '>', 100)
          ->where('title', '<>', 'Admin');
})
whereExists(function ($query) {
    $query->select(DB::raw(1))
          ->from('orders')
          ->whereRaw('orders.user_id = users.id');
})   // where exists ( select 1 from orders where orders.user_id = users.id )

# json
$users = DB::table('users')
                ->where('options->language', 'en')
                ->get();

$users = DB::table('users')
                ->where('preferences->dining->meal', 'salad')
                ->get();

orderBy('name', 'desc')
latest()                // = orderBy('created_at', 'desc')
inRandomOrder()
groupBy('account_id')->having('account_id', '>', 100)
skip(10)->take(5)        // = offset(10)->limit(5)

// $role 有值才会执行闭包
$role = $request->input('role');

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



// $role 有值执行第一个闭包，否则执行第二个闭包
$sortBy = null;

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

写

DB::table('users')->insert(
    ['email' => '[e-mail]', 'votes' => 0]
);

DB::table('users')->insert([
    ['email' => '[e-mail]', 'votes' => 0],
    ['email' => '[e-mail]', 'votes' => 0]
]);

DB::table('users')->insertGetId(
    ['email' => '[e-mail]', 'votes' => 0]
);

改

DB::table('users')
            ->where('id', 1)
            ->update(['votes' => 1]);

DB::table('users')
            ->where('id', 1)
            ->update(['options->enabled' => true]);

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']);

删

DB::table('users')->delete();
DB::table('users')->where('votes', '>', 100)->delete();
DB::table('users')->truncate();