laravel---查询语句

最近使用 larval 框架开发一些系统，但是它的查询语句常常忘记了，下面整理一些常见的 laravel 框架查询语句：

1、简单查询

$result = User::where('status',1)->get(); // 查询多条记录
$result = User::where('id',1)—>first(); // 查询单条记录

2、查询字段

$result = User::where('id',1)->select('id','name'); // 查询某几个字段
$result = User::select('id','name')->find($id); // 查询某几个字段
$result = User::select('id','name')->where('id', $id)->first(); // 查询某几个字段
$result = User::where('id', $id)->first(['id', 'name']); // 查询某几个字段

3、查询单个字段

$result = User::where('id',$id)->value('name');

4、多条查询

$result = User::where('id','<',100)->get(['id','name']); // 多条查询
$result = User::where('id','<',100)->get(['id','name'])->toArray(); // 转数组

5、获取索引数组

$result = User::where('id','<',10)->pluck('id'); // 索引数组
$result = User::where('id','<',10)->pluck('name','id');// id=>name的关联数组

6、where查询

$result = User::where('id',$id)->first();
$result = User::where('id','!=',$id)->first();
$result = User::where('status',1)->get();
$result = User::where('status','!=',0)->get();
$result = User::where([['vip', 1],['status',1]]); // 多条件

7、when查询

$list = User::when($keywords, function ($query) use ($keywords) {
  return $query->where('name', 'like', '%' . $keywords . '%');
})->orderBy('sort')->orderByDesc('id')->paginate(12);

8、模糊查询

$result = User::where('name','like','%'.$keyword.'%')->get(); // 模糊查询
$result = User::where(DB::raw('concat(title," ",firstname," ",lastname)'),'like','%'.$keyword.'%')->get(); // 多字段模糊查询

9、IN查询

$ids = '1,2,3,4';
$idArr = explode(',',$ids);
$result = User::whereIn('id',$ids)->get();
$result = User::whereIn('id',$ids)->orWhereIn('pid',$ids)->get();
$result = User::whereIn('id',[110,119,120])->get(['name']);
$result = User::whereNotIn('id',[110,119,120])->get(['name']);

10、排序

$result = User::orderBy('created_at')->get(['id','name']);
$result = User::orderBy('created_at','desc')->get(['id','name']);
$result = User::orderByDesc('created_at')->get(['id','name']);

11、with管理查询

$result = User::with('orders:user_id,id')->get(['id','name']); 
$result = User::with('orders:user_id,id')->has('orders')->get(['id','name']);
$result = User::with('orders:user_id,id')->whereHas('orders')->get(['id','name']);
$result = User::with('orders:user_id,id')->whereHas('orders',function($query){})->get(['id','name']); 

12、limit查询

$result = User::orderByDesc('created_at')->offset((3-1)*10)->limit(10)->get();

13、聚合查询

$result = User::where('active',1)->count();
$result = User::where('active',1)->max('gold');
$result = User::where('active',1)->min('gold');

14、分组查询

$result = User::whereBetween('create_at',[$startTime,$endTime])->groupBy('hobby')->get()->toArray();

15、Join关联查询

$result = Db::table('user as u')->whereRaw('u.status = 3')
    ->leftJoin('order as o','u.id','=','o.user_id')
    ->get();
$result = Db::table('user as u')
    ->join('order as o','u.id = o.user_id')
    ->orderBy('u.id','DESC')
    ->get();

16、别名

$result = User::select('name','nikename as realname')->get();
$result = User::select('name')->addSelect('nikename as realname')->get();

17、区间查询

$result = User::where('created_at','>',Carbon::now()->subDay())->get();
$result = User::where([['status', 1],['created_at', '>', Carbon::now()->subDay()]]);

未完待续...