Laravel - Database - Query Builder (官方文件原子化翻譯筆記)

# 前言

學習一個框架, Ray 的想法是, 在深入理解底層實作的原理之前, 應該先知道這個框架的 使用方法; 先學習怎麼使用這個前人造的輪子, 再學習怎麼樣造一個輪子。
所以本篇文章重點在於細讀官方文件, 並將內容理解後以 Q&A 的方式記錄下來, 加速學習以及查詢。

# Retrieving Results

# Retrieving All Rows From A Table

以下的 Laravel example code 的意思是？

• Example:

  <?php
  
  namespace App\Http\Controllers;
  
  use App\Http\Controllers\Controller;
  use Illuminate\Support\Facades\DB;
  
  class UserController extends Controller
  {
      public function index()
      {
          $users = DB::table('users')->get();
  
          return view('user.index', ['users' => $users]);
      }
  }

• Answer:
  取得 users table 中所有的 records, 並將資料帶到 ‘views/user/index’ page

# Retrieving A Single Row / Column From A Table

以下的 Laravel example code 的意思是？

• Example:

  <?php
  $email = DB::table('users')->where('name', 'John')->value('email');

• Answer:
  只取得符合 where(‘name’, ‘John’) 條件的眾多 user model 中, 第一個 model 中的 ‘email’ column 的值

以下的 Laravel example code 的意思是？

• Example:

  <?php
  $user = DB::table('users')->where('name', 'John')->first();
  
  echo $user->name;

• Answer:
  從 Query Builder 的結果, collection of models 中, 回傳第一個 model

# Retrieving A List Of Column Values

以下的 Laravel example code 的意思是？

• Example:

  <?php
  $titles = DB::table('roles')->pluck('title');
  
  foreach ($titles as $title) {
      echo $title;
  }

• Answer:
  取得 roles collection 中的 title column data

# Chunking Results

以下的 Laravel example code 的意思是？

• Example:

  <?php
  DB::table('users')->orderBy('id')->chunk(100, function ($users) {
      // Process the records...
  
      return false;
  });

• Answer:
  return false 終止 chunk

以下的 Laravel example code 的意思是？

• Example:

  <?php
  DB::table('users')->where('active', false)
      ->chunkById(100, function ($users) {
          foreach ($users as $user) {
              DB::table('users')
                  ->where('id', $user->id)
                  ->update(['active' => true]);
          }
      });

• Answer:
  chunkById 預設會使用 orderBy id, 並且會記住上一個 chunk 的 last id
  效能上使用 chunkById 更好, 因為 chunk 是採用 offset … limit, 這樣當資料越來越多的時候, 會造成不必要的效能浪費, 而 chunkById 是採用 where id > $lastId, 因為效能上較佳
  再者, 因為 chunkById 預設會 orderby id, 所以不會有 chunk 跟 chunk 之間因為順序錯亂而丟失 item 的情況

# Aggregates

以下的 Laravel example code 的意思是？

• Example:

  <?php
  $users = DB::table('users')->count();

• Answer:
  取得 users table 中總共的資料筆數

以下的 Laravel example code 的意思是？

• Example:

  <?php
  $price = DB::table('orders')->max('price');

• Answer:
  可得 orders table 中, price column value 最大的那筆資料

# Determining If Records Exist

以下的 Laravel example code 的意思是？

• Example:

  <?php
  return DB::table('orders')->where('finalized', 1)->exists();
  
  return DB::table('orders')->where('finalized', 1)->doesntExist();

• Answer:
  判斷該 model 是否存在, return boolean

# Selects

# Specifying A Select Clause

以下的 Laravel example code 的意思是？

• Example:

  <?php
  $users = DB::table('users')->select('name', 'email as user_email')->get();

• Answer:
  只取得 'name', 'email' as user_email 這兩個欄位

以下的 Laravel example code 的意思是？

• Example:

  <?php
  $query = DB::table('users')->select('name');
  
  $users = $query->addSelect('age')->get();

• Answer:
  使用 addSelect(), 在原本既存的 $query 多 select 一個 column

# Raw Expressions

以下的 Laravel example code 的意思是？

• Example:

  <?php
  $users = DB::table('users')
                       ->select(DB::raw('count(*) as user_count, status'))
                       ->where('status', '<>', 1)
                       ->groupBy('status')
                       ->get();

• Answer:
  使用 DB::row(), 裡頭直接帶入 DB raw 語法

以下的 Laravel example code 的意思是？

• Example:

  <?php
  $orders = DB::table('orders')
                  ->selectRaw('price * ? as price_with_tax', [1.0825])
                  ->get();

• Answer:
  若要使用 select 的 mysql raw 語法, 可使用 selectRaw()

以下的 Laravel example code 的意思是？

• Example:

  <?php
  $orders = DB::table('orders')
                  ->whereRaw('price > IF(state = "TX", ?, 100)', [200])
                  ->get();

• Answer:
  使用 MySQL whereRaw expression, price > (如果 state = "TX", 那就是 200, 否則就是 100)

以下的 Laravel example code 的意思是?

• Example:

  <?php
  $orders = DB::table('orders')
                  ->orderByRaw('updated_at - created_at DESC')
                  ->get();

• Answer:
  使用 orderByRaw 取得 update_at 減掉 created_at 的時間, 再以這個時間排序

以下的 Laravel example code 的意思是？

• Example:

  <?php
  $orders = DB::table('orders')
                  ->select('city', 'state')
                  ->groupByRaw('city, state')
                  ->get();

• Answer:
  若要使用 raw group by constraint, 可使用 groupByRaw()

# Joins

# Inner Join Clause

以下的 Laravel example code 的意思是？

• Example:

  <?php
  $users = DB::table('users')
              ->join('contacts', 'users.id', '=', 'contacts.user_id')
              ->join('orders', 'users.id', '=', 'orders.user_id')
              ->select('users.*', 'contacts.phone', 'orders.price')
              ->get();

• Answer:
  使用 join 取得多張 table 資料

# Left Join / Right Join Clause

以下的 Laravel example code 的意思是？

• Example:

  <?php
  $users = DB::table('users')
              ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
              ->get();

• Answer:
  leftJoin Posts table, 因為是 left join, 會將 Users table 全部列出, 以及與之相關的 Posts records, 若該 User record 並無相關的 Post record, Post 的 record 會以 null 顯示

以下的 Laravel example code 的意思是？

• Example:

  <?php
  $users = DB::table('users')
              ->rightJoin('posts', 'users.id', '=', 'posts.user_id')
              ->get();

• Answer:
  若要 rightJoin, 可使用 rightJoin(), 會取得 posts table 上所有的欄位, 包括 posts.user_id 為 null 的欄位也會取出

# Cross Join Clause

以下的 Laravel example code 的意思是？

• Example:

  <?php
  $sizes = DB::table('sizes')
              ->crossJoin('colors')
              ->get();

• Answer:
  cross join colors table
  如果沒有 where constraints, cross join 會回傳兩張表的乘積

# Advanced Join Clauses

以下的 Laravel example code 的意思是？

• Example:

  <?php
  DB::table('users')
          ->join('contacts', function ($join) {
              $join->on('users.id', '=', 'contacts.user_id')->orOn(...);
          })
          ->get();

• Answer:
  join contacts table, 在 closure 內可定義比較複雜的 join 條件

以下的 Laravel example code 的意思是？

• Example:

  <?php
  DB::table('users')
          ->join('contacts', function ($join) {
              $join->on('users.id', '=', 'contacts.user_id')
                   ->where('contacts.user_id', '>', 5);
          })
          ->get();

• Answer:
  join 並且附加額外的 where clause

# Subquery Joins

以下的 Laravel example code 的意思是？

• Example:

  <?php
  $latestPosts = DB::table('posts')
                     ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
                     ->where('is_published', true)
                     ->groupBy('user_id');
  
  $users = DB::table('users')
          ->joinSub($latestPosts, 'latest_posts', function ($join) {
              $join->on('users.id', '=', 'latest_posts.user_id');
          })->get();

• Answer:
  使用 joinSub() join $latestPosts 這個 subquery, arg1 為 subquery, arg2 定義 subquery 的 table alias

# Unions

以下的 Laravel example code 的意思是？

• Example:

  <?php
  $first = DB::table('users')
              ->whereNull('first_name');
  
  $users = DB::table('users')
              ->whereNull('last_name')
              ->union($first)
              ->get();

• Answer:
  使用 union 一次取出兩個 query 不重複的資料

# Where Clauses

# Simple Where Clauses

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')->where('votes', '=', 100)->get();

• Answer:
  從 users table 當中 query 出 votes column 的值等於 100 的 records

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')->where('votes', 100)->get();

• Answer:
  從 users table 當中 query 出 votes column 的值等於 100 的 records

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')->where([
      ['status', '=', '1'],
      ['subscribed', '<>', '1'],
  ])->get();

• Answer:
  相當於 where(‘status’, ‘=’, ‘1’)->where(‘subscribed’, ‘<>’, ‘1’)

# Or Statements

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
                      ->where('votes', '>', 100)
                      ->orWhere('name', 'John')
                      ->get();

• Answer:
  取得第一個 where clause 或 第二個 where clause 的 query 結果, 簡單來說, 誰找到的都算數

# Or Statements

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
              ->where('votes', '>', 100)
              ->orWhere(function($query) {
                  $query->where('name', 'Abigail')
                        ->where('votes', '>', 50);
              })
              ->get();

• Answer:
  // SQL: select * from users where votes > 100 or (name = ‘Abigail’ and votes > 50)

# Additional Where Clauses

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
             ->whereBetween('votes', [1, 100])
             ->get();

• Answer:
  query column votes 介於 1~100 的 records

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
                      ->whereNotBetween('votes', [1, 100])
                      ->get();

• Answer:
  query column votes 不介於 1~100 的 records

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
                      ->whereIn('id', [1, 2, 3])
                      ->get();

• Answer:
  query records, 其 id column 的 value 為 array 中其中一個值

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
                      ->whereNotIn('id', [1, 2, 3])
                      ->get();

• Answer:
  query records, 其 id column 的 value 不為 array 中的任何一個值

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
                      ->whereNull('updated_at')
                      ->get();

• Answer:
  query records, 其 updated_at 的值為 null

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
                      ->whereNotNull('updated_at')
                      ->get();

• Answer:
  query records, 其 updated_at 的值不為 null

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
                  ->whereDate('created_at', '2016-12-31')
                  ->get();

• Answer:
  query records, 其 created_at 的 value 為 date 格式, 且為 ‘2016-12-31’

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
                  ->whereMonth('created_at', '12')
                  ->get();

• Answer:
  query, 其 created_at column 的 value 轉成 date 格式後, month 須為 12

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
                  ->whereDay('created_at', '31')
                  ->get();

• Answer:
  query, 其 created_at column 的 value 轉成 date 格式後, day 須為 31

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
                  ->whereYear('created_at', '2016')
                  ->get();

• Answer:
  query, 其 created_at column 的 value 轉成 date 格式後, year 須為 2016

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
                  ->whereTime('created_at', '=', '11:20:45')
                  ->get();

• Answer:
  query, 其 created_at column 的 value 轉成 date 格式後, time 須為 ‘11:20:45’

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
                  ->whereColumn('first_name', 'last_name')
                  ->get();

• Answer:
  query, 其 first_name column 的 value 需與 last_name column 的 value 相等

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
                  ->whereColumn('updated_at', '>', 'created_at')
                  ->get();

• Answer:
  query, 其 first_name column 的 value 需大於 created_at column value

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
                  ->whereColumn([
                      ['first_name', '=', 'last_name'],
                      ['updated_at', '>', 'created_at'],
                  ])->get();

• Answer:
  一次附加多個 whereColumn clauses

# Parameter Grouping

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
             ->where('name', '=', 'John')
             ->where(function ($query) {
                 $query->where('votes', '>', 100)
                       ->orWhere('title', '=', 'Admin');
             })
             ->get();

• Answer:
  在一個 where closure 內附加多個 where clauses

# Where Exists Clauses

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
             ->whereExists(function ($query) {
                 $query->select(DB::raw(1))
                       ->from('orders')
                       ->whereRaw('orders.user_id = users.id');
             })
             ->get();

• Answer:

  # 如果 select 1 from orders ... 有返回的話, 回傳 select * from users
  select * from users
  where exists (
      select 1 from orders where orders.user_id = users.id
  )

# Subquery Where Clause

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  use App\User;
  
  $users = User::where(function ($query) {
      $query->select('type')
          ->from('membership')
          ->whereColumn('user_id', 'users.id')
          ->orderByDesc('start_date')
          ->limit(1);
  }, 'Pro')->get();

• Answer:
  如果 subquery 取得的 type === ‘Pro’ 的話, 取得 users, 也就是說, 會取得最新有 membership 的 user

# JSON Where Clauses

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
                  ->where('options->language', 'en')
                  ->get();

• Answer:
  取得 users, 當其 JSON column options 中的 language key 的 value 為 ‘en’

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
                  ->where('preferences->dining->meal', 'salad')
                  ->get();

• Answer:
  取得 users, 當其 JSON column preferences 中的 dining 中的 meal key 的 value 為 ‘salad’

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
                  ->whereJsonContains('options->languages', 'en')
                  ->get();

• Answer:
  取 users, 其 options 欄位中的 languages 是一個 array, 其 value 含有 ‘en’

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
                  ->whereJsonContains('options->languages', ['en', 'de'])
                  ->get();

• Answer:
  取 users, 其 options 欄位中的 languages 是一個 array, 其 values 含有 ‘en’ 以及 ‘de’

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
                  ->whereJsonLength('options->languages', 0)
                  ->get();

• Answer:
  取 users, 其 options 欄位中的 languages 是一個 array, 其 length 為 0

# Ordering, Grouping, Limit & Offset

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
                  ->orderBy('name', 'desc')
                  ->orderBy('email', 'asc')
                  ->get();

• Answer:
  取 users, 依據 orderBy 的參數排序指定的欄位以及正序或倒序

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $user = DB::table('users')
                  ->latest()
                  ->first();

• Answer:
  根據 date 做排序, 預設使用 created_at 欄位

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $randomUser = DB::table('users')
                  ->inRandomOrder()
                  ->first();

• Answer:
  使用隨機排序

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $query = DB::table('users')->orderBy('name');
  
  $usersOrderedByEmail = $query->reorder('email', 'desc')->get();

• Answer:
  移除現有的 orderBy constraint, 套用新的排序規則

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
                  ->groupBy('first_name', 'status')
                  ->having('account_id', '>', 100)
                  ->get();

• Answer:
  先依據給予的欄位 groupBy record, 在使用 having 篩選出需要的資料, having 在此相當於 where 的作用

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')
                  ->offset(10)
                  ->limit(5)
                  ->get();

• Answer:
  跳過十筆資料, 然後只取五筆, 也可用 skip 以及 take

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $users = DB::table('users')->skip(10)->take(5)->get();

• Answer:
  跳過十筆資料, 然後只取五筆, 也可用 offset 以及 limit

# Conditional Clauses

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $sortBy = null;
  
  $users = DB::table('users')
                  ->when($sortBy, function ($query, $sortBy) {
                      return $query->orderBy($sortBy);
                  }, function ($query) {
                      return $query->orderBy('name');
                  })
                  ->get();

• Answer:
  當 when 的第一個 parameter 為 true 時, 方執行第二個 parameter closure, 若第一個 parameter 為 false, 則執行第二個 closure

# Inserts

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  DB::table('users')->insert([
      ['email' => 'taylor@example.com', 'votes' => 0],
      ['email' => 'dayle@example.com', 'votes' => 0],
  ]);

• Answer:
  insert 多個 record 到 users table

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  DB::table('users')->insertOrIgnore([
      ['id' => 1, 'email' => 'taylor@example.com'],
      ['id' => 2, 'email' => 'dayle@example.com'],
  ]);

• Answer:
  在 insert 過程中, 如有重複的 record, 自動忽略

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $id = DB::table('users')->insertGetId(
      ['email' => 'john@example.com', 'votes' => 0]
  );

• Answer:
  自動取得 auto-increment ID, 預設欄位名稱為 id, 若要特別指定欄位名稱, 可帶入第二個 parameter

# Updates

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $affected = DB::table('users')
                ->where('id', 1)
                ->update(['votes' => 1]);

• Answer:
  update 指定 record

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  DB::table('users')
      ->updateOrInsert(
          ['email' => 'john@example.com', 'name' => 'John'],
          ['votes' => '2']
      );

• Answer:
  如果 parameter 1 的條件有找到 record 的話, 使用 parameter 2 指定的資料更新該 record, 如果沒找到, 會使用兩個 parameter 的資料來建立一個新的 record

# Updating JSON Columns

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  $affected = DB::table('users')
                ->where('id', 1)
                ->update(['options->enabled' => true]);

• Answer:
  更新 JSON Columns

# Increment & Decrement

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  DB::table('users')->increment('votes');
  
  DB::table('users')->increment('votes', 5);
  
  DB::table('users')->decrement('votes');
  
  DB::table('users')->decrement('votes', 5);
  
  DB::table('users')->increment('votes', 1, ['name' => 'John']);

• Answer:

  <?php
  // 直接將 votes column 的 value 加 1
  DB::table('users')->increment('votes');
  
  // 直接將 votes column 的 value 加 5
  DB::table('users')->increment('votes', 5);
  
  // 直接將 votes column 的 value 減 1
  DB::table('users')->decrement('votes');
  
  // 直接將 votes column 的 value 減 5
  DB::table('users')->decrement('votes', 5);
  
  // 除了將 votes column 加 1 之外, 還更新 name column, 更新為 John
  DB::table('users')->increment('votes', 1, ['name' => 'John']);

Laravel Query Builder 當中, 當我使用 increment 或 decrement method 時, 會觸發 model event 嗎？

不會

# Deletes

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  DB::table('users')->delete();
  
  DB::table('users')->where('votes', '>', 100)->delete();

• Answer:

  <?php
  // 刪除 users table 中的所有 records
  DB::table('users')->delete();
  
  // 刪除 users table 中指定的 records
  DB::table('users')->where('votes', '>', 100)->delete();

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  DB::table('users')->truncate();

• Answer:
  刪除所有資料庫的資料, 並且重置 auto-incrementing ID

# Pessimistic Locking

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  DB::table('users')->where('votes', '>', 100)->sharedLock()->get();

• Answer:
  鎖住指定的 rows, 在解除鎖定之前, 這些 rows 無法被 update

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();

• Answer:
  鎖住指定的 rows, 在解除鎖定之前, 這些 rows 無法被 update, 也無法被 select

# Debugging

Laravel Query Builder 當中, 以下的 example 意思是？

• Example:

  <?php
  DB::table('users')->where('votes', '>', 100)->dd();
  
  DB::table('users')->where('votes', '>', 100)->dump();

• Answer:

  <?php
  // 印出 debug information, 並且停止執行該 request
  DB::table('users')->where('votes', '>', 100)->dd();
  
  // 印出 debug information, 繼續執行該 request
  DB::table('users')->where('votes', '>', 100)->dump();