SQL常見操作 · laravel5

[TOC] # 分組組 Group By ~~~ use DB; //SELECT `gender`,COUNT(`gender`) as `count` FROM `users` GROUP BY `gender` User::groupBy('gender')->get(['gender', DB::raw('COUNT(`gender`) as `count`')]); // gender count // 1 234 // 2 123 ~~~ > 在需要使用SQL語句的情況下，可使用 DB::raw() 來包裹 # 別名 AS ## 字段別名 ~~~ //SELECT `id` as `uid`, `username` as `account` FROM `users` WHERE `id` = 1 $user = User::find(1, ['id as uid', 'username as account']); $user->uid; //1 ~~~ ## 表別名 ~~~ //SELECT `users`.*, `b`.`role_id` FROM `users` INNER JOIN `role_user` AS `b` ON `b`.`user_id` = `users`.`id` WHERE `b`.`role_id` = 99 User::join('role_user as b', 'b.user_id', '=', 'users.id', 'INNER')->where('b.role_id', 99)->get(['users.*', 'b.role_id']) ~~~ # 實際調用 * Model中的 where、whereIn、orderBy、groupBy、join等方法，都是通過 Illuminate\Database\Eloquent\Builder Illuminate\Database\Query\Builder 來實現的。 * Model中的find，create，destroy，update 都是封裝的一些常規方法 # 模型的 boot() 方法在一個 Eloquent 模型中，有個神奇的地方，叫 boot()，在那里，你可以覆蓋默認的行為： ~~~ class User extends Model { public static function boot() { parent::boot(); static::updating(function($model) { // 寫點日志啥的 // 覆蓋一些屬性，類似這樣 $model->something = transform($something); }); } } ~~~ 在創建模型對象時設置某些字段的值，大概是最受歡迎的例子之一了。一起來看看在創建模型對象時，你想要生成 UUID 字段該怎么做。 ~~~ public static function boot() { parent::boot(); self::creating(function ($model) { $model->uuid = (string)Uuid::generate(); }); } ~~~ # 模型特性：時間、追加等 Eloquent模型有些參數，使用類的屬性形式。最常用是： ~~~ class User extends Model { protected $table = 'users'; protected $fillable = ['email', 'password']; // 可以被批量賦值字段，如 User::create() 新增時，可使用字段 protected $dates = ['created_at', 'deleted_at']; // 需要被Carbon維護的字段名 protected $appends = ['field1', 'field2']; // json返回時，附加的字段 } ~~~ 不只這些，還有： ~~~ protected $primaryKey = 'uuid'; // 更換主鍵 public $incrementing = false; // 設置不自增長 protected $perPage = 25; // 定義分頁每頁顯示數量（默認15） const CREATED_AT = 'created_at'; const UPDATED_AT = 'updated_at'; //重寫時間字段名 public $timestamps = false; // 設置不需要維護時間字段 ~~~ 還有更多,我只列出了一些有意思的特性，具體參考文檔 [abstract Model class](https://github.com/laravel/framework/blob/5.6/src/Illuminate/Database/Eloquent/Model.php) 了解所有特性. # WhereX 有一種優雅的方式能將這種代碼： ~~~ $users = User::where('approved', 1)->get(); ~~~ 轉換成這種： ~~~ $users = User::whereApproved(1)->get(); ~~~ 對，你沒有看錯，使用字段名作為后綴添加到 where 后面，它就能通過魔術方法運行了。另外，在 Eloquent 里也有些和時間相關的預定義方法： ~~~ User::whereDate('created_at', date('Y-m-d')); User::whereDay('created_at', date('d')); User::whereMonth('created_at', date('m')); User::whereYear('created_at', date('Y')); ~~~ # 通過關系排序一個復雜一點的「技巧」。你想對論壇話題按最新發布的帖子來排序？論壇中最新更新的主題在最前面是很常見的需求，對吧？首先，為主題的最新帖子定義一個單獨的關系： ~~~ public function latestPost() { return $this->hasOne(\App\Post::class)->latest(); } ~~~ 然后，在控制器中，我們可以實現這個「魔法」： ~~~ $users = Topic::with('latestPost')->get()->sortByDesc('latestPost.created_at'); ~~~ # `Eloquent::when() -- 不再使用 if-else` 很多人都喜歡使用"if-else"來寫查詢條件，像這樣： ~~~ if (request('filter_by') == 'likes') { $query->where('likes', '>', request('likes_amount', 0)); } if (request('filter_by') == 'date') { $query->orderBy('created_at', request('ordering_rule', 'desc')); } ~~~ 有一種更好的方法 -- 使用 when() ~~~ $query = Author::query(); $query->when(request('filter_by') == 'likes', function ($q) { return $q->where('likes', '>', request('likes_amount', 0)); }); $query->when(request('filter_by') == 'date', function ($q) { return $q->orderBy('created_at', request('ordering_rule', 'desc')); }); ~~~ 它可能看上去不是很優雅，但它強大的功能是傳遞參數： ~~~ $query = User::query(); $query->when(request('role', false), function ($q, $role) { return $q->where('role_id', $role); }); $authors = $query->get(); ~~~ # BelongsTo 默認模型假如有一個 Post 模型附屬于 Author 模型，在 Blade 模板里可以寫作如下代碼： ~~~ {{ $post->author->name }} ~~~ 但是如果作者被刪除了，或者因為某些原因未設置？你就會得到一個錯誤信息，類似「不存在的對象屬性」。那么，你可以這么避免它： ~~~ {{ $post->author->name ?? '' }} ~~~ 但是你可以在 Eloquent 關系模型級別做到這種效果： ~~~ public function author() { return $this->belongsTo('App\Author')->withDefault(); } ~~~ 這個例子中，如果帖子沒有作者的話，author() 關系方法會返回一個空的 App\Author 模型對象。此外，我們還可以為默認模型分配一個默認的屬性值。 ~~~ public function author() { return $this->belongsTo('App\Author')->withDefault([ 'name' => 'Guest Author' ]); } ~~~ # 通過賦值函數進行排序想象一下你有這樣的代碼: ~~~ function getFullNameAttribute() { return $this->attributes['first_name'] . ' ' . $this->attributes['last_name']; } ~~~ 現在,你想要通過 `"full_name"` 進行排序? 發現是沒有效果的: ~~~ $clients = Client::orderBy('full_name')->get(); //沒有效果 ~~~ 解決辦法很簡單.我們需要在獲取結果后對結果進行排序. ~~~ $clients = Client::get()->sortBy('full_name'); // 成功! ~~~ 注意的是方法名稱是不相同的 -- 它不是orderBy,而是sortBy # 全局作用域下的默認排序如果你想要 User::all() 總是按照 name 字段來排序呢？你可以給它分配一個全局作用域。讓我們回到 boot() 這個我們在上文提到過的方法： ~~~ protected static function boot() { parent::boot(); // 按照 name 正序排序 static::addGlobalScope('order', function (Builder $builder) { $builder->orderBy('name', 'asc'); }); } ~~~ 擴展閱讀 [查詢作用域](https://laravel-china.org/docs/laravel/5.6/eloquent#query-scopes) # 原生查詢方法有時候，我們需要在 Eloquent 語句中添加原生查詢。幸運的是，確實有這樣的方法。 ~~~ // whereRaw $orders = DB::table('orders') ->whereRaw('price > IF(state = "TX", ?, 100)', [200]) ->get(); // havingRaw Product::groupBy('category_id')->havingRaw('COUNT(*) > 1')->get(); // orderByRaw User::where('created_at', '>', '2016-01-01') ->orderByRaw('(updated_at - created_at) desc') ->get(); ~~~ # 復制：復制一行的副本很簡單。說明不是很深入，下面是復制數據庫實體（一條數據）的最佳方法： ~~~ $task = Tasks::find(1); $newTask = $task->replicate(); $newTask->save(); ~~~ # Chunk() 方法之大塊數據與 Eloquent 不完全相關,它更多的關于 Collection (集合),但是對于處理大數據集合,仍然是很有用的。你可以使用 chunk() 將這些數據分割成小數據塊修改前: ~~~ $users = User::all(); foreach ($users as $user) { // ... ~~~ 你可以這樣做: ~~~ User::chunk(100, function ($users) { foreach ($users as $user) { // ... } }); ~~~ # 創建模型時創建額外的東西我們都知道Artisan命令： ~~~ php artisan make:model Company ~~~ 但是，你知道有三個有用的標記可以為模型生成相關文件嗎？ ~~~ php artisan make:model Company -mcr ~~~ * -m 將創建一個遷移文件 * -c 將創建一個控制器 * -r 表示控制器應該是一個資源控制器 # 調用 save 方法的時候指定 updated_at 你知道 `->save() `方法可以接受參數嗎? 我們可以通過傳入參數阻止它的默認行為：更新 updated_at 的值為當前時間戳。 ~~~ $product = Product::find($id); $product->updated_at = '2019-01-01 10:00:00'; $product->save(['timestamps' => false]); ~~~ 這樣，我們成功在 save 時指定了 updated_at 的值。 # update() 的結果是什么你是否想知道這段代碼實際上返回什么？ ~~~ $result = $products->whereNull('category_id')->update(['category_id' => 2]); ~~~ 我是說，更新操作是在數據庫中執行的，但 $result 會包含什么？答案是受影響的行。因此如果你想檢查多少行受影響，你不需要額外調用其他任何內容 -- update() 方法會給你返回此數字。 # 把括號轉換成 Eloquent 查詢如果你有個 and 和 or 混合的 SQL 查詢，像這樣子的： ~~~ ... WHERE (gender = 'Male' and age >= 18) or (gender = 'Female' and age >= 65) ~~~ 怎么用 Eloquent 來翻譯它呢？下面是一種錯誤的方式： ~~~ $q->where('gender', 'Male'); $q->orWhere('age', '>=', 18); $q->where('gender', 'Female'); $q->orWhere('age', '>=', 65); ~~~ 順序就沒對。正確的打開方式稍微復雜點，使用閉包作為子查詢： ~~~ $q->where(function ($query) { $query->where('gender', 'Male') ->where('age', '>=', 18); })->orWhere(function($query) { $query->where('gender', 'Female') ->where('age', '>=', 65); }) ~~~ # 復數參數的 orWhere 終于，你可以傳遞陣列參數給 orWhere()。平常的方式： ~~~ $q->where('a', 1); $q->orWhere('b', 2); $q->orWhere('c', 3); ~~~ 你可以這樣做： ~~~ $q->where('a', 1); $q->orWhere(['b' => 2, 'c' => 3]); ~~~