通過 Laravel 查詢構建器實現復雜的查詢語句 · php開發筆記

查詢小技巧我們首先來介紹幾個 Laravel 自帶的語法糖，可以幫助我們快速獲取期望的查詢結果，提高編碼效率。有時候，我們想要獲取的并不是一行或幾行記錄，而是某個字段的值，你當然你可以查詢到一行記錄后從結果對象中獲取指定字段的值，但是 Laravel 為我們提供了更便捷的語法：代碼語言：javascript 復制 ``` $name = '學院君'; $email = DB::table('users')->where('name', $name)->value('email'); ``` 這樣，通過 value 方法返回的就是指定字段的值，無需做額外的判斷和提取操作。如果你想要判斷某個字段值在數據庫中是否存在對應記錄，可以通過 exists 方法快速實現：代碼語言：javascript 復制 ``` $exists = DB::table('users')->where('name', $name)->exists(); ``` 如果存在，返回 true，否則返回 false。該方法還有一個與之相對的方法 doesntExist()。你一定有過這樣的經歷，從數據庫獲取指定查詢結果后，以主鍵 ID 值為鍵，以某個字段值為值構建關聯數組，以前，你可能不得不遍歷查詢結果構建數組才能解決這樣的問題，在 Laravel 中，我們只需在查詢構建器上調用 pluck 方法即可：代碼語言：javascript 復制 ``` $users = DB::table('users')->where('id', '<', 10)->pluck('name', 'id'); ``` 該查詢返回的結果如下：注意，我們在傳遞參數到 pluck 方法的時候，鍵對應的字段在后面，值對應的字段在前面。此外，有的時候，我們從數據庫返回的結果集比較大，一次性返回進行處理有可能會超出 PHP 內存限制，這時候，我們可以借助 chunk 方法將其分割成多個的組塊依次返回進行處理：代碼語言：javascript 復制 $names = []; DB::table('users')->orderBy('id')->chunk(5, function ($users) use (&$names) { foreach ($users as $user) { $names[] = $user->name; } }); 以上代碼的意思是對 users 按照 id 字段升序排序，然后將獲取的結果集每次返回5個進行處理，將用戶名依次放到 n a m e s 數組中。打印 names，結果如下：聚合函數在開發后臺管理系統時，經常需要對數據進行統計、求和、計算平均值、最小值、最大值等，對應的方法名分別是 count、sum、avg、min、max：代碼語言：javascript 復制 ``` $num = DB::table('users')->count(); # 計數 9 $sum = DB::table('users')->sum('id'); # 求和 45 $avg = DB::table('users')->avg('id'); # 平均值 5 $min = DB::table('users')->min('id'); # 最小值 1 $max = DB::table('users')->max('id'); # 最大值 9 ``` 高級 Where 查詢前面我們已經用到過通過 where 方法構建查詢子句，這里我們將系統介紹 WHERE 查詢子句的各種構建。基本查詢基本查詢最基本的 WHERE 查詢子句就是通過 where 方法進行簡單查詢了：代碼語言：javascript 復制 ``` DB::table('posts')->where('views', 0)->get(); # 此處等號可以省略 DB::table('posts')->where('views', '>', 0)->get(); DB::table('posts')->where('views', '<>', 0)->get(); ``` 第一個參數表示字段名，第二個參數表示運算符（支持SQL所有運算符），第三個參數表示比較值。 like查詢有時候我們可能會對字段進行模糊查詢，尤其是字符串匹配的時候：代碼語言：javascript 復制 DB::table('posts')->where('title', 'like', 'Laravel學院%')->get(); and查詢如果有多個 WHERE 條件怎么辦？在查詢構建器中，可以通過方法鏈輕松搞定：代碼語言：javascript 復制 DB::table('posts')->where('id', '<', 10)->where('views', '>', 0)->get(); 上述代碼表示獲取 where id < 10 and views > 0 的數據庫記錄，更多的條件用更多的 where 方法即可。此外，我們還可以通過傳入數組參數的方式實現上述代碼同樣的功能：代碼語言：javascript 復制 ``` DB::table('posts')->where([ ['id', '<', 10], ['views', '>', 0] ])->get(); ``` or查詢在日常查詢中，or 條件的查詢也很常見，在查詢構建器中，可以通過 orWhere 方法來實現：代碼語言：javascript 復制 DB::table('posts')->where('id', '<', 10)->orWhere('views', '>', 0)->get(); 上述代碼表示獲取 where id < 10 or views > 0 的數據庫記錄，多個 and 查詢可以通過多個 where 方法連接，同理，多個 or 查詢也可以通過多個 orWhere 方法連接。 between查詢在一些涉及數字和時間的查詢中，BETWEEN 語句可以排上用場，用于獲取在指定區間的記錄。在查詢構建器中，我們可以通過 whereBetween 方法來實現 between 查詢：代碼語言：javascript 復制 DB::table('posts')->whereBetween('views', [10, 100])->get(); 上述代碼表示獲取 where view between 10 and 100 的數據庫記錄。與之相對的還有一個 whereNotBetween 方法，用于獲取不在指定區間的數據庫記錄：代碼語言：javascript 復制 DB::table('posts')->whereNotBetween('views', [10, 100])->get(); 對應的 WHERE 條件是 where views not between 10 and 100。你可以看出來 between 語句是可以通過 and/or 查詢來替代的，只不過使用 between 語句會更簡單明了。 in查詢 IN 查詢也很常見，比如我們需要查詢的字段值是某個序列集合的子集的時候。IN 查詢可以通過 whereIn 方法來實現：代碼語言：javascript 復制 DB::table('posts')->whereIn('user_id', [1, 3, 5, 7, 9])->get(); 對應的 WHERE 子句是 where user_id in (1, 3, 5, 7, 9)。使用該方法時，需要注意傳遞給 whereIn 的第二個參數不能是空數組，否則會報錯。同樣，與之相對的，還有一個 whereNotIn 方法，表示與 whereIn 相反的查詢條件。將上述代碼中的 whereIn 方法改為 whereNotIn，對應的查詢子句就是 where user_id not in (1, 3, 5, 7, 9)。 null查詢 NULL 查詢就是判斷某個字段是否為空的查詢，Laravel 查詢構建器為我們提供了 whereNull 方法用于實現該查詢：代碼語言：javascript 復制 DB::table('users')->whereNull('email_verified_at')->get(); 對應的 WHERE 查詢子句是 where email_verified_at is null，同樣，該方法也有與之相對的 whereNotNull 方法，例如，要進行 where email_verified_at is not null 查詢，可以這么實現：代碼語言：javascript 復制 DB::table('users')->whereNotNull('email_verified_at')->get(); 日期查詢關于日常查詢，查詢構建器為我們提供了豐富的方法，從年月日到具體的時間都有覆蓋：代碼語言：javascript 復制 DB::table('posts')->whereYear('created_at', '2018')->get(); # 年 DB::table('posts')->whereMonth('created_at', '11')->get(); # 月 DB::table('posts')->whereDay('created_at', '28')->get(); # 一個月的第幾天 DB::table('posts')->whereDate('created_at', '2018-11-28')->get(); # 具體日期 DB::table('posts')->whereTime('created_at', '14:00')->get(); # 時間上面這幾個方法同時還支持 orWhereYear、orWhereMonth、orWhereDay、orWhereDate、orWhereTime。字段相等查詢有的時候，我們并不是在字段和具體值之間進行比較，而是在字段本身之間進行比較，查詢構建器提供了 whereColumn 方法來實現這一查詢：代碼語言：javascript 復制 DB::table('posts')->whereColumn('updated_at', '>', 'created_at')->get(); 對應的 WHERE 查詢子句是 where updated_at > created_at。 JSON查詢從 MySQL 5.7 開始，數據庫字段原生支持 JSON 類型，對于 JSON 字段的查詢，和普通 where 查詢并無區別，只是支持對指定 JSON 屬性的查詢：代碼語言：javascript 復制 DB::table('users') ->where('options->language', 'en') ->get(); 如果屬性字段是個數組，還支持通過 whereJsonContains 方法對數組進行包含查詢：代碼語言：javascript 復制 DB::table('users') ->whereJsonContains('options->languages', 'en_US') ->get(); DB::table('users') ->whereJsonContains('options->languages', ['en_US', 'zh_CN']) ->get(); 高級查詢參數分組除了以上這些常規的 WHERE 查詢之外，查詢構建器還支持更加復雜的查詢語句，考慮下面這個 SQL 語句：代碼語言：javascript 復制 select * from posts where id <= 10 or (views > 0 and created_at < '2018-11-28 14:00'); 貌似我們通過前面學到的方法解決不了這個查詢語句的構造，所以我們需要引入更復雜的構建方式，那就是引入匿名函數的方式（和連接查詢中構建復雜的連接條件類似）：代碼語言：javascript 復制 DB::table('posts')->where('id', '<=', 10)->orWhere(function ($query) { $query->where('views', '>', 0) ->whereDate('created_at', '<', '2018-11-28') ->whereTime('created_at', '<', '14:00'); })->get(); 在這個匿名函數中傳入的 $query 變量也是一個查詢構建器的實例。這一查詢構建方式叫做「參數分組」，在帶括號的復雜 WHERE 查詢子句中都可以參考這種方式來構建查詢語句。 WHERE EXISTS 此外，我們還可以通過查詢構建器提供的 whereExists 方法構建 WHERE EXISTS 查詢：代碼語言：javascript 復制 DB::table('users') ->whereExists(function ($query) { $query->select(DB::raw(1)) ->from('posts') ->whereRaw('posts.user_id = users.id'); }) ->get(); 對應的 SQL 語句是：代碼語言：javascript 復制 select * from `users` where exists (select 1 from `posts` where posts.user_id = users.id); 用于查詢發布過文章的用戶。子查詢有時候，我們會通過子查詢關聯不同的表進行查詢，考慮下面這個 SQL 語句：代碼語言：javascript 復制 select * from posts where user_id in (select id from users where email_verified_at is not null); 對于這條 SQL 語句，我們可以通過查詢構建器提供的子查詢來實現：代碼語言：javascript 復制 $users = DB::table('users')->whereNotNull('email_verified_at')->select('id'); $posts = DB::table('posts')->whereInSub('user_id', $users)->get(); 除了 IN 查詢外，普通的 WHERE 查詢也可以使用子查詢，對應的方法是 whereSub，但是子查詢的效率不如連接查詢高，所以我們下面來探討連接查詢在查詢構建器中的使用。連接查詢相關術語在介紹連接查詢之前，你需要對 SQL 的幾種連接查詢有所了解，SQL 連接查詢通常分為以下幾種類型：內連接：使用比較運算符進行表間的比較，查詢與連接條件匹配的數據，可細分為等值連接和不等連接等值連接（=）：如 select * from posts p inner join users u on p.user_id = u.id 不等連接（<、>、<>等）：如 select * from posts p inner join users u on p.user_id <> u.id 外鏈接：左連接：返回左表中的所有行，如果左表中的行在右表中沒有匹配行，則返回結果中右表中的對應列返回空值，如 select * from posts p left join users u on p.user_id = u.id 右連接：與左連接相反，返回右表中的所有行，如果右表中的行在左表中沒有匹配行，則結果中左表中的對應列返回空值，如 select * from posts p right join users u on p.user_id = u.id 全連接：返回左表和右表中的所有行。當某行在另一表中沒有匹配行，則另一表中的列返回空值，如 select * from posts p full join users u on p.user_id = u.id 交叉連接：也稱笛卡爾積，不帶 where 條件子句，它將會返回被連接的兩個表的笛卡爾積，返回結果的行數等于兩個表行數的乘積，如果帶 where，返回的是匹配的行數。如 select * from posts p cross join users u on p.user_id = u.id 看文字太抽象，圖示就很明了了: 注：在寫 SQL 語句時，OUTER 可以省略。創建并填充 posts 表為了方便下面的演示，我們新建一個 posts 數據表，首先創建對應遷移文件：代碼語言：javascript 復制 php artisan make:migration create_posts_table --create=posts 編寫新增的遷移文件對應遷移類 CreatePostsTable 的 up 方法如下：代碼語言：javascript 復制 public function up() { Schema::create('posts', function (Blueprint $table) { $table->increments('id'); $table->string('title')->comment('標題'); $table->text('content')->comment('內容'); $table->integer('user_id')->unsigned()->default(0); $table->integer('views')->unsigned()->default(0)->comment('瀏覽數'); $table->index('user_id'); $table->timestamps(); }); } 運行 php artisan migrate 創建 posts 數據表。然后為該數據表創建一個模型類：代碼語言：javascript 復制 php artisan make:model Post 接下來，我們為這個模型類創建一個模型工廠：代碼語言：javascript 復制 php artisan make:factory PostFactory --model=Post 編寫模型工廠 database/factories/PostFactory.php 代碼如下：代碼語言：javascript 復制 <?php use Faker\Generator as Faker; $factory->define(\App\Post::class, function (Faker $faker) { return [ 'title' => $faker->title, 'content' => $faker->text, 'user_id' => mt_rand(1, 15), 'views' => $faker->randomDigit ]; }); 然后為 posts 表創建填充類：代碼語言：javascript 復制 php artisan make:seeder PostsTableSeeder 在 database/seeds 目錄下新生成的填充類 PostsTableSeeder 中，調用模型工廠填充數據表：代碼語言：javascript 復制 <?php use Illuminate\Database\Seeder; class PostsTableSeeder extends Seeder { /** * Run the database seeds. * * @return void */ public function run() { factory(\App\Post::class, 30)->create(); } } 這樣，我們就可以運行如下 Artisan 命令填充 posts 數據表了：代碼語言：javascript 復制 php artisan db:seed --class=PostsTableSeeder 內連接首先我們來看內連接在查詢構建器中如何實現，以等值連接為例：代碼語言：javascript 復制 $posts = DB::table('posts') ->join('users', 'users.id', '=', 'posts.user_id') ->select('posts.*', 'users.name', 'users.email') ->get(); 對照前面的等值連接示例 SQL，很容易理解這段代碼，其對應的 SQL 語句是：代碼語言：javascript 復制 select posts.*, users.name, users.email from posts inner join users on users.id = posts.user_id; 在查詢構建器中我們通過 join 方法來實現內連接（包含等值連接和不等連接）。上面通過查詢構建器查詢的結果是：注：當兩張表有字段名相同的字段，并且這兩個字段都包含在 select 方法指定的字段中，需要為其中一個字段取別名，否則會產生沖突，例如，假設 posts 表中也包含 name 字段，那么需要為 users.name 取個別名：select('posts.*', 'users.name as username', 'users.email')。左連接左連接也可稱作左外連接，在查詢構建器中，可以通過 leftJoin 方法實現：代碼語言：javascript 復制 $posts = DB::table('posts') ->leftJoin('users', 'users.id', '=', 'posts.user_id') ->select('posts.*', 'users.name', 'users.email') ->get(); 對應的 SQL 語句是：代碼語言：javascript 復制 select posts.*, users.name, users.email from posts left join users on users.id = posts.user_id; 在本例中，由于每個 posts.user_id 都有對應的 users 記錄，所以，上述查詢結果和等值連接查詢結果一致。右連接右連接也可稱作右外鏈接，在查詢構建器中，可以通過 rightJoin 方法實現：代碼語言：javascript 復制 $posts = DB::table('posts') ->rightJoin('users', 'users.id', '=', 'posts.user_id') ->select('posts.*', 'users.name', 'users.email') ->get(); 對應的 SQL 語句如下：代碼語言：javascript 復制 select posts.*, users.name, users.email from posts right join users on users.id = posts.user_id; 在本例中，不是每個用戶都有對應的 posts 記錄，所以會出現某些 posts 記錄為空的結果：其它連接語句上面三種是比較常見的連接語句，查詢構建器沒有提供單獨的方法支持全連接，但是有對交叉連接的支持，對應的方法 crossJoin，使用方法如上面幾種查詢類似，這里不再單獨演示了。更加復雜的連接條件有時候，你的連接查詢條件可能比較復雜，比如下面這種：代碼語言：javascript 復制 select posts.*, users.name, users.email from posts inner join users on users.id = posts.user_id and users.email_verified_at is not null where posts.views > 0; 這個時候，我們可以通過匿名函數來組裝連接查詢的條件來構建上面的查詢語句：代碼語言：javascript 復制 $posts = DB::table('posts') ->join('users', function ($join) { $join->on('users.id', '=', 'posts.user_id') ->whereNotNull('users.email_verified_at'); }) ->select('posts.*', 'users.name', 'users.email') ->where('posts.views', '>', 0) ->get(); 我們可以在匿名函數的 $join 實例上調用所有 Where 查詢子句，以組裝我們需要的連接查詢條件。上述查詢會將對應用戶郵箱未驗證的，文章瀏覽數為 0 的所以結果過濾掉：聯合查詢查詢構建器還支持通過 union 方法合并多個查詢結果：代碼語言：javascript 復制 $posts_a = DB::table('posts')->where('views', 0); $posts_b = DB::table('posts')->where('id', '<=', 10)->union($posts_a)->get(); 通過上面這段代碼，我們將 views = 0 和 id <= 10 這兩個查詢結果合并到了一起：對應的 SQL 語句是：代碼語言：javascript 復制 (select * from `posts` where `id` <= 10) union (select * from `posts` where `views` = 0) 此外，查詢構建器也支持 UNION ALL 查詢，對應的方法是 unionAll，該方法與 union 的區別是允許重復記錄，將上述代碼中的 union 方法改為 unionAll，會發現查詢結果中包含一條重復記錄：排序對數據庫進行查詢免不了對查詢結果進行排序，查詢構建器為此提供了 orderBy 方法，比如我們想要對文章列表按照創建時間進行逆序排序，可以這么做：代碼語言：javascript 復制 $users = DB::table('posts') ->orderBy('created_at', 'desc') ->get(); 對應的 SQL 語句如下：代碼語言：javascript 復制 select * from `posts` order by `created_at` desc; 如果是升序排序，可以這么實現：代碼語言：javascript 復制 DB::table('posts')->orderBy('created_at')->get(); 默認排序規則就是升序，所以第二個參數 asc 可以省略。查詢構建器還支持通過 inRandomOrder 方法進行隨機排序：代碼語言：javascript 復制 DB::table('posts')->inRandomOrder()->get(); 注：對于較小的結果集可以使用隨機排序，結果集很大的話不要使用，因為性能比較差。分組查詢構建器還提供了 groupBy 方法用于對結果集進行分組：代碼語言：javascript 復制 $posts = DB::table('posts') ->groupBy('user_id') ->selectRaw('user_id, sum(views) as total_views') ->get(); 上述代碼對應的 SQL 語句是：代碼語言：javascript 復制 select user_id, sum(views) as total_views from `posts` group by `user_id`; 用于從 user_id 維度統計每個用戶發布文章的總瀏覽數：如果我們想要進一步對分組結果進行過濾，可以使用 having 方法，比如，要從上述分組結果中過濾出總瀏覽數大于等于 10 的記錄，可以這么做：代碼語言：javascript 復制 $posts = DB::table('posts') ->groupBy('user_id') ->selectRaw('user_id, sum(views) as total_views') ->having('total_views', '>=', 10) ->get(); 對應的 SQL 語句是：代碼語言：javascript 復制 select user_id, sum(views) as total_views from `posts` group by `user_id` having `total_views` >= 10; 對應的查詢結果是：分頁日常開發中，另一個常見的查詢場景就是分頁查詢了，在查詢構建器中提供了兩種方式來進行分頁查詢。第一種是通過 skip 方法和 take 方法組合進行分頁，skip 方法傳入的參數表示從第幾條記錄開始，take 傳入的參數表示一次獲取多少條記錄：代碼語言：javascript 復制 $posts = DB::table('posts')->orderBy('created_at', 'desc') ->where('views', '>', 0) ->skip(10)->take(5) ->get(); 對應的 SQL 語句是：代碼語言：javascript 復制 select * from `posts` where `views` > 0 order by `created_at` desc limit 5 offset 10; 該查詢會先按照查詢條件和排序條件進行過濾和排序，然后從第10條記錄開始獲取5條記錄返回。另一種是通過 offset 方法和 limit 方法組合進行分頁查詢，offset 表示從第幾條記錄開始，limit 表示一次獲取多少條記錄，使用方式和 skip 和 take 類似：代碼語言：javascript 復制 $posts = DB::table('posts')->orderBy('created_at', 'desc') ->where('views', '>', 0) ->offset(10)->limit(5) ->get(); 對應的 SQL 語句如下：代碼語言：javascript 復制 select * from `posts` where `views` > 0 order by `created_at` desc limit 5 offset 10; 底層最終執行的 SQL 語句完全一樣，所以，隨便你選擇哪種方式都是可以的。原生查詢如果上面介紹的構建方式還是不能滿足你的需求，無法構建出你需要的 SQL 查詢語句，那么可以考慮通過查詢構建器提供的原生查詢方法來構建查詢。查詢構建器提供的原生查詢支持請參考官方文檔，里面說的比較詳細，這里就不再贅述了；如果查詢構建器提供的原生方法還不能滿足你的需求，那只有使用 DB 門面進行徹底的原生查詢操作了。