Laravel-Query Builder

Introduction

Query Builder( Query產生器,後面還是用原文表示 )提供一個方便流暢的介面去建立或是執行queries。這可以被用來執行應用程式大部分的資料庫操作,而且可以在所有支援的資料庫系統皆能運作。

注意: Laravel的 query builder 使用 PDO 變數綁定來保護您的應用程式防止 SQL Injection,因此您不需要再去花費功夫過濾被綁定的字串。

Selects

從表單取得所有的列
<?php

$users = DB::table('users')->get();

foreach ($users as $user)
{
    var_dump($user->name);
}
從表單取得一個列
<?php

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

var_dump($user->name);
從一列中取得一欄
<?php

$name = DB::table('users')->where('name', 'John')->pluck('name');

從表單中取得某欄的值
<?php

$roles = DB::table('roles')->lists('title');

此方法會回傳一個 title 的陣列,您也可以給回傳的陣列設定一個鍵值。

<?php

$roles = DB::table('roles')->lists('title', 'name');

指定一個 Select 子句
<?php

$users = DB::table('users')->select('name', 'email')->get();

$users = DB::table('users')->distinct()->get();

$users = DB::table('users')->select('name as user_name')->get();
新增一個Select子句至以存在的 Query
<?php

$query = DB::table('users')->select('name');

$users = $query->addSelect('age')->get();
使用Where操作子
<?php

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

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

$users = DB::table('users')
  ->whereBetween('votes', array(1, 100))
  ->get()
;
使用 Where not between
<?php

$users = DB::table('users')
  ->whereNotBetween('votes', array(1, 100))
  ->get()
;
使用Where IN
<?php

$users = DB::table('users')
 ->whereIn('id', array(1, 2, 3))
 ->get()
;

$users = DB::table('users')
 ->whereNotIn('id', array(1, 2, 3))
 ->get()
;
使用 whereNull 找尋沒有給值的記錄
<?php

$users = DB::table('users')
 ->whereNull('updated_at')
 ->get()
;
Order By, Group By, Having
<?php

$users = DB::table('users')
  ->orderBy('name', 'desc')
  ->groupBy('count')
  ->having('count', '>', 100)
  ->get()
;
Offset & Limit
<?php

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

Joins

Query Builder 可以使用 join 語句,如下:

最基本的 JOIN 語句:
<?php

DB::table('users')
  ->join('contacts', 'users.id', '=', 'contacts.user_id')
  ->join('orders', 'users.id', '=', 'orders.user_id')
  ->select('users.id', 'contacts.phone', 'orders.price')
;

Left Join 語句:
<?php

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

您也可以指定更多進階的 join 子句:

<?php

DB::table('users')
  ->join('contacts', function($join)
  {
    $join->on('users.id', '=', 'contacts.user_id')->orOn(...);
  })
  ->get()
;
如果您想要用 where 形式的子句在您的join上,您可以使用 where 和 onWhere 方法。
<?php

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

Advanced Wheres

有時候您可能會需要新增更多的進階where子句例如 "where exists",或是嵌套一組變數群。Laravel 的 Query Builder 也能夠處理這些:

Parameter Grouping( 把變數聚集 )
<?php

DB::table('users')
  ->where('name', '=', 'John')
  ->orWhere(function($query)
  {
    $query->where('votes', '>', 100)->where('title', '<>', 'Admin');
  })
  ->get()
;
上面的 query 會產生入下 SQL
SELECT * FROM users WHERE name = 'John' OR (votes > 100 AND title <> 'Admin')
Exists 語句
<?php

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

select * from users
where exists (
    select 1 from orders where orders.user_id = users.id
)

Aggregates

Query Builder 也提供了許多統計的方法,例如 count、max、min、avg、以及 sum。

使用統計方法
<?php

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

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

$price = DB::table('orders')->min('price');

$price = DB::table('orders')->avg('price');

$total = DB::table('users')->sum('votes');

Raw Expressions

有時候您需要在query中使用原生的表達式。這些表達式會以字串的形態被注入到 query中,所以使用時要小心 SQL injection 的問題。您可以使用 DB::raw() 方法來新增一個原生的表達式。

使用原生表達式
<?php

$users = DB::table('users')
  ->select(DB::raw('count(*) as user_count, status'))
  ->where('status', '<>', 1)
  ->groupBy('status')
  ->get()
;
增加或減少某個欄位的數值
<?php

DB::table('users')->increment('votes');

DB::table('users')->increment('votes', 5);

DB::table('users')->decrement('votes');

DB::table('users')->decrement('votes', 5);
您也可以額外指定需要改變的欄位
<?php

DB::table('users')->increment('votes', 1, array('name' => 'John'));

Inserts

新增一筆記錄到表格中
<?php

DB::table('users')->insert(
    array('email' => 'john@example.com', 'votes' => 0)
);

如果表格有一個自動累加的 id,使用 insertGetId()方法新增一筆記錄並且取得 id。

<?php

$id = DB::table('users')->insertGetId(
    array('email' => 'john@example.com', 'votes' => 0)
);

注意: 當使用PostgreSQL時,使用 insertGetId() 方法取得的自動累加欄位的欄位名稱必須為 "id"。

新增多筆記錄到表格中
<?php

DB::table('users')->insert(array(
    array('email' => 'taylor@example.com', 'votes' => 0),
    array('email' => 'dayle@example.com', 'votes' => 0),
));

Updates

更新table某筆記錄
<?php

DB::table('users')
  ->where('id', 1)
  ->update(array('votes' => 1))
;

Deletes

刪除table中某個範圍的記錄
<?php

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

DB::table('users')->delete();
truncate(其實也是刪除所有記錄,只是 autoincrement 會重設) 一個table
<?php

DB::table('users')->truncate();

Unions

Query Builder也提供了一個快速將兩個 queries 聯集的方法:

執行Query的聯集
<?php

$first = DB::table('users')->whereNull('first_name');

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

union() 也可以寫作 unionAll(),效果是一樣的。

Pessimistic Locking

以 "分享鎖" 的方式執行 Select 語句,您可以使用 sharedLock() 方法:

<?php

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

將 Select語句加上 "更新鎖",可以使用 lockForUpdate() 方法:

<?php

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

Caching Queries

您可以使用 remember()方法很簡單的將 query 的結果做緩存:

緩存一個Query的結果
<?php

$users = DB::table('users')->remember(10)->get();

在這個例子中,query 的結果會被緩存10分鐘。當結果被緩存後,query 不會再次訪問 database,而是直接從預設的快取驅動器取得快取。

如果您的快取驅動器有支援 tag 功能,可以使用如下:( 預設的 file 機制不支援tag )

<?php

$users = DB::table('users')->cacheTags(array('people', 'authors'))->remember(10)->get();

本篇回顧

和 Doctrine 的 Query Builder比起來,Laravel 真的簡單多了,或許 Doctrine 比較強大,但是我們不見得需要使用到如此多的功能。一般網站需要的 CRUD 功能,Laravel 的 Query Builder 看起來其實已經綽綽有餘了。接下來是 Eloquent ORM 的介紹囉,這應該是重頭戲中的重頭戲了,真期待!

參考資料

Laravel Database Eloquent ORM: http://laravel.com/docs/queries

Comments

comments powered by Disqus