[laravel 5.4] Database Query Builder
29 ส.ค. 2017 , 10,115 Views , หมวดหมู่ Laravel ทุกหมวดหมู่ โค๊ดดิ้ง , ป้ายกำกับ:laravelRetrieving Results
ดึงข้อมูลทั้งหมด
ข้อมูลออกมาเป็น array
|
1 2 3 4 5 6 |
$users = DB::table('users')->get(); //select * from `users` foreach ($users as $user) { echo $user->name; } |
ดึงข้อมูลมาแถวเดียว
|
1 2 3 4 |
$user = DB::table('users')->where('name', 'John')->first(); //select * from `users` where `name` = ? limit 1 echo $user->name; |
ดึงมาเฉพาะ column ที่ต้องการ
|
1 2 |
$email = DB::table('users')->where('name', 'John')->value('email'); //select `email` from `users` where `name` = ? limit 1 |
ดึง column เดียวออกมาทั้งหมด
ข้อมูลออกมาเป็น array
|
1 2 3 4 5 6 |
$names = DB::table('users')->pluck('name'); //select `name` from `users` foreach ($names as $name) { echo $name; } |
ระบุค่า index,value ของ array ที่จะให้ออกมาได้
|
1 2 3 4 5 6 |
$names = DB::table('users')->pluck('name','id'); //select `name`, `id` from `users` foreach ($names as $id => $name) { echo $name; } |
Chunking Results
คือการแบ่ง Query ออกเป็นช่วงๆ จะช่วยประหยัดหน่วยความจำ(memory) เมื่อทำงานกับชุดผลลัพธ์ขนาดใหญ่ ตัวอย่างข้างล่างคือ ระบบจะ query ครั้งล่ะ 200 rows ไปจนกว่าจะสิ้นสุด
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DB::table('customers')->orderBy('id')->chunk(200, function ($customers) { foreach ($customers as $customer) { // } }); //select * from `customers` order by `id` asc limit 200 offset 0 //select * from `customers` order by `id` asc limit 200 offset 200 //select * from `customers` order by `id` asc limit 200 offset 400 //select * from `customers` order by `id` asc limit 200 offset 600 //select * from `customers` order by `id` asc limit 200 offset 800 //select * from `customers` order by `id` asc limit 200 offset 1000 //select * from `customers` order by `id` asc limit 200 offset 1200 //select * from `customers` order by `id` asc limit 200 offset 1400 |
สามารถหยุดการ query ได้ด้วย return false
|
1 2 3 4 5 |
DB::table('customers')->orderBy('id')->chunk(200, function ($customers) { // Process the records... return false; }); |
Aggregates
สามารถใช้ count, max, min, avg, และsum ตามตัวอย่างด้านล่าง
|
1 2 3 4 5 |
$customers = DB::table('customers')->count(); //select count(*) as aggregate from `customers` $price = DB::table('orders')->max('price'); //select max(`price`) as aggregate from `orders` |
สามารถใช้ร่วมกับฟังค์ชันอื่นๆได้
|
1 2 3 |
$price = DB::table('orders') ->where('finalized', 1) ->avg('price'); |
Selects
เลือกเฉพาะ column ที่ต้องการ
|
1 2 |
$users = DB::table('users')->select('name', 'email as user_email')->get(); //select `name`, `email` as `user_email` from `users` |
distinct
|
1 2 |
$users = DB::table('users')->distinct()->get(); //select distinct * from `users` |
addSelect
|
1 2 3 4 |
$query = DB::table('users')->select('name'); $users = $query->addSelect('age')->get(); //select `name`, `email` from `users` |
Raw Expressions
บางครั้งหากต้องการแทรกคำสั่ง SQL เข้าไป สามารถใช้ DB::raw ได้
|
1 2 3 4 5 |
$users = DB::table('users') ->select(DB::raw('count(*) as user_count, status')) ->where('status', '<>', 1) ->groupBy('status') ->get(); |
Joins
Inner Join
|
1 2 3 4 5 |
$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(); |
Left Join
|
1 2 3 |
$users = DB::table('users') ->leftJoin('posts', 'users.id', '=', 'posts.user_id') ->get(); |
Cross Join
|
1 2 3 |
$users = DB::table('sizes') ->crossJoin('colours') ->get(); |
Advanced Join
|
1 2 3 4 5 |
DB::table('users') ->join('contacts', function ($join) { $join->on('users.id', '=', 'contacts.user_id')->orOn(...); }) ->get(); |
Unions
|
1 2 3 4 5 6 7 |
$first = DB::table('users') ->whereNull('first_name'); $users = DB::table('users') ->whereNull('last_name') ->union($first) ->get(); |
Where
Simple Where Clauses
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
$users = DB::table('users')->where('votes', '=', 100)->get(); //select * from `users` where `votes` = ? $users = DB::table('users')->where('votes', 100)->get(); //select * from `users` where `votes` = ? $users = DB::table('users') ->where('votes', '>=', 100) ->get(); //select * from `users` where `votes` >= ? $users = DB::table('users') ->where('votes', '<>', 100) ->get(); //select * from `users` where `votes` <> ? $users = DB::table('users') ->where('name', 'like', 'T%') ->get(); select * from `users` where `name` like ? $users = DB::table('users')->where([ ['status', '=', '1'], ['subscribed', '<>', '1'], ])->get(); //select * from `users` where (`status` = ? and `subscribed` <> ?) |
Or Statements
|
1 2 3 4 5 |
$users = DB::table('users') ->where('votes', '>', 100) ->orWhere('name', 'John') ->get(); //select * from `users` where `votes` > ? or `name` = ? |
Additional Where Clauses
whereBetween
|
1 2 3 |
$users = DB::table('users') ->whereBetween('votes', [1, 100])->get(); //select * from `users` where `votes` between ? and ? |
whereNotBetween
|
1 2 3 4 |
$users = DB::table('users') ->whereNotBetween('votes', [1, 100]) ->get(); //select * from `users` where `id` not between ? and ? |
whereIn / whereNotIn
whereIn
|
1 2 3 4 |
$users = DB::table('users') ->whereIn('id', [1, 2, 3]) ->get(); //select * from `users` where `id` in (?, ?, ?) |
whereNotIn
|
1 2 3 4 |
$users = DB::table('users') ->whereNotIn('id', [1, 2, 3]) ->get(); //select * from `users` where `id` not in (?, ?, ?) |
whereNull / whereNotNull
whereNull
|
1 2 3 4 |
$users = DB::table('users') ->whereNull('updated_at') ->get(); //select * from `users` where `updated_at` is null |
whereNotNull
|
1 2 3 4 |
$users = DB::table('users') ->whereNotNull('updated_at') ->get(); //select * from `users` where `updated_at` is not null |
whereDate / whereMonth / whereDay / whereYear
whereDate
|
1 2 3 4 |
$users = DB::table('users') ->whereDate('created_at', '2016-12-31') ->get(); //select * from `users` where date(`created_at`) = ? |
whereMonth
|
1 2 3 4 |
$users = DB::table('users') ->whereMonth('created_at', '12') ->get(); //select * from `users` where month(`created_at`) = ? |
whereDay
|
1 2 3 4 |
$users = DB::table('users') ->whereDay('created_at', '31') ->get(); //select * from `users` where day(`created_at`) = ? |
whereYear
|
1 2 3 4 |
$users = DB::table('users') ->whereYear('created_at', '2016') ->get(); //select * from `users` where year(`created_at`) = ? |
whereColumn
ตรวจสอบเงื่อนไขระหว่ง column
|
1 2 3 4 |
$users = DB::table('users') ->whereColumn('first_name', 'last_name') ->get(); //select * from `users` where `first_name` = `last_name` |
สามารถใช้ Comparison operator ได้
|
1 2 3 4 |
$users = DB::table('users') ->whereColumn('updated_at', '>', 'created_at') ->get(); //select * from `users` where `updated_at` > `created_at` |
ใส่ได้หลาย Conditions
|
1 2 3 4 5 6 |
$users = DB::table('users') ->whereColumn([ ['first_name', '=', 'last_name'], ['updated_at', '>', 'created_at'] ])->get(); //select * from `users` where (`first_name` = `last_name` and `updated_at` > `created_at`) |
Parameter Grouping
|
1 2 3 4 5 6 7 8 |
DB::table('users') ->where('name', '=', 'John') ->orWhere(function ($query) { $query->where('votes', '>', 100) ->where('title', '<>', 'Admin'); }) ->get(); //select * from users where name = 'John' or (votes > 100 and title <> 'Admin') |
หากต้องการส่ง parameter เข้าไป
|
1 2 3 4 5 6 7 8 9 10 |
$title = 'Admin'; DB::table('users') ->where('name', '=', 'John') ->orWhere(function ($query) use ($title) { $query->where('votes', '>', 100) ->where('title', '<>', $title); }) ->get(); //select * from users where name = 'John' or (votes > 100 and title <> ?) |
Where Exists
|
1 2 3 4 5 6 7 8 9 10 11 |
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 //) |
JSON Where Clauses
Laravel also supports querying JSON column types on databases that provide support for JSON column types.
|
1 2 3 4 5 6 7 |
$users = DB::table('users') ->where('options->language', 'en') ->get(); $users = DB::table('users') ->where('preferences->dining->meal', 'salad') ->get(); |
Conditional Clauses
ตรวจสอบค่าของตัวแปล หากมีค่าก็ให้ทำตาม condition
|
1 2 3 4 5 6 7 |
$role = $request->input('role'); $users = DB::table('users') ->when($role, function ($query) use ($role) { return $query->where('role_id', $role); }) ->get(); |
หาก role มีค่า คำส่ง SQL ก็จะเป็น
|
1 |
select * from `users` where `role_id` = ? |
หาก role ไม่มีค่าหรือเป็นค่าว่าง คำสั่ง SQL ก็จะเป็น
|
1 |
select * from `users` |
เงื่อนไขข้างล่างหาก sortBy ไม่มีค่าหรือมีค่า null ก็จะทำตามเงื่อนไขที่สอง
|
1 2 3 4 5 6 7 8 9 |
$sortBy = null; $users = DB::table('users') ->when($sortBy, function ($query) use ($sortBy) { return $query->orderBy($sortBy); }, function ($query) { return $query->orderBy('name'); }) ->get(); |
คำสั่ง SQL ก็จะออกมาเป็น
|
1 |
select * from `users` order by `name` asc |
Ordering, Grouping, Limit, & Offset
orderBy
|
1 2 3 |
$users = DB::table('users') ->orderBy('name', 'desc') ->get(); |
latest / oldest
latest คือล่าสุด(desc) oldest คือเก่าสุด(asc) โดยค่าเริ่มต้นระบบจะจัดเรียงตาม created_at หากต้องการจัดเรียงตาม column อื่นสามารถระบุชื่อ column ได้
|
1 2 3 4 |
$user = DB::table('users') ->latest() ->first(); //select * from `users` order by `created_at` desc limit 1 |
ระบุชื่อ column
|
1 2 3 4 |
$user = DB::table('users') ->oldest('name') ->first(); //select * from `users` order by `name` asc limit 1 |
inRandomOrder
ระบบจะ Sort แบบสุ่มมา
|
1 2 3 4 |
$randomUser = DB::table('users') ->inRandomOrder() ->first(); //select * from `users` order by RAND() limit 1 |
groupBy / having / havingRaw
|
1 2 3 4 5 |
$users = DB::table('users') ->groupBy('account_id') ->having('account_id', '>', 100) ->get(); //select * from `users` group by `account_id` having `account_id` > 100 ? |
havingRaw
|
1 2 3 4 5 |
$users = DB::table('orders') ->select('department', DB::raw('SUM(price) as total_sales')) ->groupBy('department') ->havingRaw('SUM(price) > 2500') ->get(); |
skip / take
|
1 2 |
$users = DB::table('users')->skip(10)->take(5)->get(); select * from `users` limit 5 offset 10 |
หรือใช้โค๊ดด้านล่างก็ได้เช่นกัน
|
1 2 3 4 5 |
$users = DB::table('users') ->offset(10) ->limit(5) ->get(); //select * from `users` limit 5 offset 10 |
Inserts
|
1 2 3 |
DB::table('users')->insert( ['email' => 'john@example.com', 'votes' => 0] ); |
หลายๆเรคคอร์ด
|
1 2 3 4 |
DB::table('users')->insert([ ['email' => 'taylor@example.com', 'votes' => 0], ['email' => 'dayle@example.com', 'votes' => 0] ]); |
Auto-Incrementing IDs
หาก id ในตารางเป็นแบบ auto-incrementing id สามารถใช้ insertGetId พร้อมทั้ง รีเทรินค่าเป็น ID ที่ Insert ด้วย
|
1 2 3 |
$id = DB::table('users')->insertGetId( ['email' => 'john@example.com', 'votes' => 0] ); |
Updates
|
1 2 3 |
DB::table('users') ->where('id', 1) ->update(['votes' => 1]); |
Updating JSON Columns
|
1 2 3 |
DB::table('users') ->where('id', 1) ->update(['options->enabled' => true]); |
Increment & Decrement
การเพิ่มและลดค่า
|
1 2 3 4 5 6 7 8 9 10 11 |
DB::table('users')->increment('votes'); //update `users` set `votes` = `votes` + 1 DB::table('users')->increment('votes', 5); //update `users` set `votes` = `votes` + 5 DB::table('users')->decrement('votes'); //update `users` set `votes` = `votes` - 1 DB::table('users')->decrement('votes', 5); //update `users` set `votes` = `votes` - 5 |
สามารถใส่ column และ value เพิ่มเติมเพื่ออัพเดทได้
|
1 2 |
DB::table('users')->increment('votes', 1, ['name' => 'John']); //update `users` set `votes` = `votes` + 1, `name` = ? |
Deletes
|
1 2 3 4 |
DB::table('users')->delete(); //delete from `users` DB::table('users')->where('votes', '>', 100)->delete(); |
หากต้องการลบข้อมูลทั้งหมดและรีเซ็นค่า ID ที่เป็น auto-incrementing ให้เป็น 0 สามารถใช้ truncate ได้
|
1 |
DB::table('users')->truncate(); |
Pessimistic Locking
The query builder also includes a few functions to help you do “pessimistic locking” on your selectstatements. To run the statement with a “shared lock”, you may use the sharedLock method on a query. A shared lock prevents the selected rows from being modified until your transaction commits:
|
1 |
DB::table('users')->where('votes', '>', 100)->sharedLock()->get(); |
Alternatively, you may use the lockForUpdate method. A “for update” lock prevents the rows from being modified or from being selected with another shared lock:
|
1 |
DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get(); |
ที่มา
https://laravel.com/docs/5.4/queries
Related Posts
ป้ายกำกับ:laravel
