在這個章節，要介紹 Query Builder 查詢建構器，Laravel提供資料存取的方法。Laravel提供的資料存取有兩種，一個是 Query Builder，另一個就是 Eloquent ORM，沒有強制規定要用哪一種去做資料的處理。

什麼是 Query Builder？

Query Builder 就是 查詢產生器，以 PDO(PHP Data Objects)為基底處理資料庫CURD機制，為了避免可能的 SQL資料隱碼 (SQL injection) 攻擊。

建立Member的Controller

這裡如果已經建立過Member的Controller，請直接忽略這步驟。

1
$ php artisan make:controller MemberController

建立好以後，開啟 MemberController.php

專案 > app > Http > Controllers > MemberController.php

1
2
3
4
5
6
7
8
9
10
<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

class MemberController extends Controller
{
    //
}

在class 裡面我們建立一個 function index，接下來要做CURD的操作。
會用到 DB 這個物件，所以我們要記得去引入 use Illuminate\Support\Facades\DB;。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class MemberController extends Controller
{
    public function index(){
        $data = DB::table('members')....替換的函數
        dd($data);
    }
}

接下來顯示資料的畫面，我們會以 dd($data);，來做顯示。

印出或顯示資料結果 dd() 、dump()

什麼是dd() and dump()？他是Laravel 內建的一個輔助函數，大部分開發者會拿來做debug使用。把 var_dump 寫成一個 function 後供開發者使用。

新增 insert()

1
2
3
4
5
6
7
8
9
$data = DB::table('members')->insert([
    'm_account' => 'Ps_0610', 
    'm_password' => md5('Ab123456'),
    'm_name' => '郭小黑',
    'm_id' => 'M10001',
    'm_email' => 'ya1233456@hotmail.com',
    'm_phone' => '02-23781234',
    'category' => '5'
]);

原始SQL語法

1
2
3
4
INSERT INTO members(m_account,m_password,m_name,
                    m_id,m_email,m_phone,category)
            VALUES('Ps_0610', md5('Ab123456'),'郭小黑',
                    'M10002','ya1233456@hotmail.com','02-23781234','5');

修改 update()

1
2
3
$data = DB::table('members')
            ->where('m_account','=','Ps_0610')
            ->update(['m_email' => 'Ad123456792@gmail.com']);

原始SQL語法

1
2
3
UPDATE members
SET m_email = 'Ad123456792@gmail.com'
WHERE m_account = 'Ps_0610';

刪除 delete()

1
2
3
$data = DB::table('members')
        ->where('m_account','=','Ps_0610')
        ->delete();

原始SQL語法

1
2
DELETE FROM members
WHERE m_account = 'Ps_0610';

顯示資料表的所有內容 GET()

get() 方法回傳的結果為 Illuminate\Support\Collection，每個結果都是一個 PHP StdClass。
Query Builder實例

1
$data = DB::table('members')->get();

原始SQL語法

1
SELECT * FROM members;

資料表篩選條件 WHERE()

※ 查詢會員姓名為 Odie Tromp 的。
WHERE(欄位,運算子,值)，第二個參數，運算子為等號時，可以做省略。

1
$data = DB::table('members')->where('m_name','Odie Tromp')->get();

原始SQL語法

1
2
3
SELECT * 
FROM members
WHERE m_name="Odie Tromp";

※ 查詢會員資料表編號 大於等於 6 的。

1
$data = DB::table('members')->where('id','>=','6')->get();

原始SQL語法

1
2
3
SELECT * 
FROM members
WHERE id >=6;

※ 查詢會員資料表編號 大於等於 6 且抓取第一筆first()。

(回傳單一的stdClass Object)

1
$data = DB::table('members')->where('id','>=','6')->get()->first();

原始SQL語法

1
2
3
4
SELECT * 
FROM members
WHERE id >=6
LIMIT 1;

※ 計算 會員資料表編號 大於等於 9的。

1
$data = DB::table('members')->where('id','>=','9')->count();

原始SQL語法

1
2
3
SELECT COUNT(id) 
FROM members
WHERE id>=9;

※ 查詢 會員資料表編號 等於10，回傳 m_account欄位值 value()

1
$data = DB::table('members')->where('id','10')->value('m_account');

結果

1
"gchbObOjsv"

查詢條件是否存在 exists()

查詢條件值不存在

1
$data = DB::table('members')->where('id','>=','11')->exists();

結果

1
false

查詢條件值存在

1
$data = DB::table('members')->where('id','>=','10')->exists();

結果

1
true

查詢條件是否存在(相反) doesntExist()

查詢條件值不存在

1
$data = DB::table('members')->where('id','>=','11')->doesntExist();

結果

1
true

查詢條件值存在

1
$data = DB::table('members')->where('id','>=','10')->doesntExist();

結果

1
false

查詢指定的欄位 select()

※ 只列出會員姓名(m_name)的欄位

1
2
3
4
$data = DB::table('members')->select('m_name')->get();

// 同上
$data = DB::select(DB::raw('select m_name FROM members'));

原始SQL語法

1
2
SELECT m_name
FROM members;

※ 只列出會員姓名(m_name)的欄位，且將欄位名改成name。

1
$data = DB::table('members')->select('m_name as name')->get();

原始SQL語法

1
2
SELECT m_name AS `name`
FROM members;

※ 只列出會員姓名(m_name)、會員電話(m_phone)、會員電子郵件(m_email)的欄位。

1
2
3
4
5
$data = DB::table('members')->select('m_name','m_phone','m_email')->get();

// 結果跟上面一樣
$data = DB::table('members')->select('m_name')
                            ->addSelect('m_phone','m_email')->get();

原始SQL語法

1
2
SELECT m_name,m_phone,m_email
FROM members;

特殊情況使用 selectRaw()

selectRaw() 應盡量少用，雖然是 Laravel 提供的，可是在對於 SQL Injection 防護就少了一層。
會員類別做群組，並將群組的資料做計算各別總數

1
2
3
4
$data = DB::table('members')->select('m_name','m_account','m_name','category')
                            ->selectRaw('count(*) AS category_count')
                            ->groupBy('category')
                            ->get();

原始SQL語法

1
2
3
SELECT id , m_account , m_name , category , count(*) AS category_count 
FROM members
GROUP BY category;

計算資料筆數 COUNT()

1
$data = DB::table('members')->count();

原始SQL語法

1
SELECT COUNT(*) FROM members;

查詢會員資料表編號的最大值 Max( )

1
$data = DB::table('members')->max('id');

原始SQL語法

1
SELECT MAX(id) FROM members;

關聯資料表 JOIN()

將 會員資料表的 會員權限(category) 關連到會員類別資料表的 編號(id)

1
2
3
4
$data = DB::table('members')
    ->join('member_categories','members.category','=','member_categories.id')
    ->select('members.*','member_categories.name')
    ->get();

原始SQL語法

1
2
3
4
SELECT members.*,member_categories.`name`
FROM members 
JOIN member_categories
ON members.category = member_categories.id;

排序 orderBy()

1
$data = DB::table('members')->orderBy('category','asc')->get();

原始SQL語法

1
2
3
SELECT * 
FROM members
order by category asc;

清空資料表內容 trucate()

1
$data = DB::table('members')->trucate();

原始SQL語法

1
TRUNCATE TABLE members

集合需要的欄位值 pluck()

pluck() 將 Collection 裡取得每一個 StdClass object 的某個欄位值，之後集合起來。
laravel 在5.4以後用 pluck() 方法取代以前的 lists()

※ 只有取得欄位的值放入陣列的value。
pluck('value')

1
$data = DB::table('members')->pluck('m_name');

結果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Illuminate\Support\Collection {#254 ▼
  #items: array:11 [▼
    0 => "Mrs. Maymie Kulas"
    1 => "Odie Tromp"
    2 => "Mabelle Okuneva"
    3 => "Lolita Lind MD"
    4 => "Evalyn Prohaska"
    5 => "Adriel Zulauf"
    6 => "Joe McGlynn"
    7 => "Kassandra Turner Jr."
    8 => "Ms. Mollie Rice"
    9 => "Rebekah Welch DVM"
    10 => "郭小黑"
  ]
}

※ 可自行指定需要的key鍵。但須注意的是key鍵若有遇到重複的，會以最後一筆為鍵值。

pluck('value','key')

1
$data = DB::table('members')->pluck('m_name','m_account');

結果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Illuminate\Support\Collection {#254 ▼
  #items: array:11 [▼
    "2egcFFaMjf" => "Mrs. Maymie Kulas"
    "E0hoKPmRsY" => "Odie Tromp"
    "quE8AEWiCE" => "Mabelle Okuneva"
    "WPTnOjHmCa" => "Lolita Lind MD"
    "7TLwP4gj1P" => "Evalyn Prohaska"
    "4VV6QStEDh" => "Adriel Zulauf"
    "jjKTpQmcoP" => "Joe McGlynn"
    "20jsd3NgI0" => "Kassandra Turner Jr."
    "XzHSrOH58K" => "Ms. Mollie Rice"
    "gchbObOjsv" => "Rebekah Welch DVM"
    "Ps_0611" => "郭小黑"
  ]
}

查詢傳回不重複的資料，將重複資料做剃除 distinct()

※ 只列出 會員編號，且剃除重複的。

1
$data = DB::table('members')->select('m_id')->distinct()->get();

原始SQL語法

1
2
select distinct m_id 
from members;

將會員編號進行分組，且只能顯示 會員權限4以上的。 groupBy()、having()

1
2
3
4
$data = DB::table('members')
            ->groupBy('m_id')
            ->having('category', '>', 4)
            ->get();

原始SQL語法

1
2
3
4
SELECT * 
FROM members
GROUP BY m_id
HAVING category>4;

※ 錯誤訊息

※ groupBy 無法正常執行時，解決方法(2選1)

到 config > database.php 的檔案裡找尋 mysql 的陣列。

1、 在陣列裡的strict 的value值改成 false ，groupBy 就可以正常執行。

2、 在陣列裡增加 'modes' 的key鍵及值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
'mysql' => [
    
    ...以上省略
    
    'strict' => false,
    'engine' => null,
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
    ]) : [],
   'modes' => [
        //'ONLY_FULL_GROUP_BY', // Disable this to allow grouping by one column
        'STRICT_TRANS_TABLES',
        'NO_ZERO_IN_DATE',
        'NO_ZERO_DATE',
        'ERROR_FOR_DIVISION_BY_ZERO',
        'NO_AUTO_CREATE_USER',
        'NO_ENGINE_SUBSTITUTION'
    ],
]

參考文件Laravel : Syntax error or access violation: 1055 Error

忽略幾筆資料skip()，取幾筆資料take()。

忽略7筆資料，顯示後2筆資料。

1
$data = DB::table('members')->skip(7)->take(2)->get();

結論

以上是 Query Builder 的函數簡略介紹，DB操作函數的內容真的蠻多樣，其實要用一篇全部做一個介紹，其實蠻困難的，可能要真正實戰，才會了解真正的用意，下一篇會介紹 Eloquent ORM 的使用。

標籤: w3HexSchool PHP Laravel