laravel---常用的查询构造器

今天阅读laravel文档，整理了一些常用的查询构造器：

一、获取所有行

$users = DB::table('users')->get(); // 区别 Think 的 Select

二、获取单行/列

$user = DB::table('users')->where('name', 'John')->first(); // 区别 Think 的 Find

三、列中取值

$email = DB::table('users')->where('name', 'John')->value('email');

四、获取某一列的值

$titles = DB::table('roles')->pluck('title');

$roles = DB::table('roles')->pluck('name', 'id'); // 会将id作为键 name作为键值 [1=>'管理员']

四、排序

DB::table('users')->orderBy('id')->get();
DB::table('users')->orderBy('name', 'desc')->get();

五、聚合查询

$users = DB::table('users')->count(); // 数量
$price = DB::table('orders')->max('price'); // 最大
$price = DB::table('orders')->min('price'); // 最小
$price = DB::table('orders')->avg('price'); // 平均
$price = DB::table('orders')->sum('price'); // 求和

六、判断记录是否存在

return DB::table('orders')->where('finalized', 1)->exists();
return DB::table('orders')->where('finalized', 1)->doesntExist();

七、查询指定字段

$users = DB::table('users')->select('name', 'email as user_email')->get(); // 区别 Think 的 field

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

八、去重查询

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

九、原生表达式

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

1、selectRaw

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

2、whereRaw 和 orWhereRaw 方法将原生的 where

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

3、havingRaw 和 orHavingRaw 方法可以用于将原生字符串设置为 having 语句的值

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

4、orderByRaw 方法可用于将原生字符串设置为 order by 子句的值：

$orders = DB::table('orders')
    ->orderByRaw('updated_at - created_at DESC')
    ->get();

十、Joins查询

1、内链接

$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();

2、左连接，右连接 leftJoin rightJoin 和 join方法用法相同

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

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

3、高级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();

十一、子查询

joinSub，leftJoinSub 和 rightJoinSub 方法关联一个查询作为子查询。

每个方法都会接收三个参数：子查询，表别名和定义关联字段的闭包：

$latestPosts = DB::table('posts')
   ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
   ->where('is_published', true)
   ->groupBy('user_id');

$users = DB::table('users')->joinSub($latestPosts, 'latest_posts', function ($join) {
    $join->on('users.id', '=', 'latest_posts.user_id');
})->get();

十二、联合查询

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

十三、where查询

1、简单查询

$users = DB::table('users')->where('votes', '=', 100)->get();
$users = DB::table('users')->where('votes', 100)->get();

2、模糊查询

$users = DB::table('users')->where('name', 'like', 'T%')->get();

3、多条件查询

$users = DB::table('users')->where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])->get();

4、Or查询

$users = DB::table('users')->where('votes', '>', 100)->orWhere('name', 'John')->get();

5、between查询

whereBetween / orWhereBetween / whereNotBetween / orWhereNotBetween

$users = DB::table('users')->whereBetween('votes', [1, 100])->get();
$users = DB::table('users')->whereNotBetween('votes', [1, 100])->get();

6、In查询

whereIn / whereNotIn / orWhereIn / orWhereNotIn

$users = DB::table('users')->whereIn('id', [1, 2, 3])->get();
$users = DB::table('users')->whereNotIn('id', [1, 2, 3])->get();

6、NULL查询

whereNull / whereNotNull / orWhereNull / orWhereNotNull

$users = DB::table('users')->whereNull('updated_at')->get();
$users = DB::table('users')->whereNotNull('updated_at')->get();

7、其他的神奇查询

whereDate / whereMonth / whereDay / whereYear / whereTime / whereColumn / orWhereColumn

详见：https://learnku.com/docs/laravel/6.x/queries/5171

十四、分组查询

groupBy 和 having 方法可以将结果分组

$users = DB::table('users')->groupBy('account_id')->having('account_id', '>', 100)->get();
$users = DB::table('users')->groupBy('first_name', 'status')->having('account_id', '>', 100)->get();

十五、条件查询 when

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

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

打完收工！