雅安论坛

标题: laravel---查询语句 [打印本页]
作者: 匿名    时间: 2022-11-25 10:00
标题: laravel---查询语句
本帖最后由 匿名 于 2022-11-25 10:04 编辑

最近使用 larval 框架开发一些系统，但是它的查询语句常常忘记了，下面整理一些常见的 laravel 框架查询语句：
1、简单查询
  1. $result = User::where('status',1)->get(); // 查询多条记录
  2. $result = User::where('id',1)—>first(); // 查询单条记录
复制代码

2、查询字段
  1. $result = User::where('id',1)->select('id','name'); // 查询某几个字段
  2. $result = User::select('id','name')->find($id); // 查询某几个字段
  3. $result = User::select('id','name')->where('id', $id)->first(); // 查询某几个字段
  4. $result = User::where('id', $id)->first(['id', 'name']); // 查询某几个字段
复制代码



3、查询单个字段
  1. $result = User::where('id',$id)->value('name');
复制代码

4、多条查询
  1. $result = User::where('id','<',100)->get(['id','name']); // 多条查询
  2. $result = User::where('id','<',100)->get(['id','name'])->toArray(); // 转数组
复制代码



5、获取索引数组
  1. $result = User::where('id','<',10)->pluck('id'); // 索引数组
  2. $result = User::where('id','<',10)->pluck('name','id');// id=>name的关联数组
复制代码



6、where查询
  1. $result = User::where('id',$id)->first();
  2. $result = User::where('id','!=',$id)->first();
  3. $result = User::where('status',1)->get();
  4. $result = User::where('status','!=',0)->get();
  5. $result = User::where([['vip', 1],['status',1]]); // 多条件
复制代码


7、when查询
  1. $list = User::when($keywords, function ($query) use ($keywords) {  return $query->where('name', 'like', '%' . $keywords . '%');})->orderBy('sort')->orderByDesc('id')->paginate(12);
复制代码

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



9、IN查询
  1. $ids = '1,2,3,4';$idArr = explode(',',$ids);
  2. $result = User::whereIn('id',$ids)->get();
  3. $result = User::whereIn('id',$ids)->orWhereIn('pid',$ids)->get();
  4. $result = User::whereIn('id',[110,119,120])->get(['name']);
  5. $result = User::whereNotIn('id',[110,119,120])->get(['name']);
复制代码



10、排序
  1. $result = User::orderBy('created_at')->get(['id','name']);
  2. $result = User::orderBy('created_at','desc')->get(['id','name']);
  3. $result = User::orderByDesc('created_at')->get(['id','name']);
复制代码



11、with管理查询
  1. $result = User::with('orders:user_id,id')->get(['id','name']);
  2. $result = User::with('orders:user_id,id')->has('orders')->get(['id','name']);
  3. $result = User::with('orders:user_id,id')->whereHas('orders')->get(['id','name']);
  4. $result = User::with('orders:user_id,id')->whereHas('orders',function($query){})->get(['id','name']);
复制代码



12、limit查询
  1. $result = User::orderByDesc('created_at')->offset((3-1)*10)->limit(10)->get();
复制代码

13、聚合查询
  1. $result = User::where('active',1)->count();
  2. $result = User::where('active',1)->max('gold');
  3. $result = User::where('active',1)->min('gold');
复制代码



14、分组查询
  1. $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、别名
  1. $result = User::select('name','nikename as realname')->get();
  2. $result = User::select('name')->addSelect('nikename as realname')->get();
复制代码



17、区间查询
  1. $result = User::where('created_at','>',Carbon::now()->subDay())->get();
  2. $result = User::where([['status', 1],['created_at', '>', Carbon::now()->subDay()]]);
复制代码








欢迎光临 雅安论坛 (https://www.yaanbbs.net/) Powered by Discuz! X3.4