laravel---查询语句
本帖最后由 匿名 于 2022-11-25 10:04 编辑最近使用 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',)->get(['name']);
$result = User::whereNotIn('id',)->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()]]);
页:
[1]