Ra mắt hai series mới cực hot Trí tuệ nhân tạo A đến ZPython công cụ không thể thiếu khi nghiên cứu Data science, Machine learning.

Xây dựng truy vấn bằng Laravel Query Builder

Trong phần trước Làm việc với cơ sở dữ liệu trong Laravel bạn đã bắt đầu làm quen với việc thực hiện các câu truy vấn SQL dạng thô (raw SQL query), một khó khăn trong việc sử dụng các câu truy vấn này là việc truyền giá trị vào câu truy vấn. Các ứng dụng thực tế thường có các câu truy vấn rất phức tạp, nếu sử dụng raw SQL query sẽ rất khó khăn. Để giải quyết vấn đề này, Laravel đưa ra Laravel Query Builder giúp bạn có thể xây dựng bất kỳ câu truy vấn nào dù nó phức tạp đến đâu. Laravel Query Builder sử dụng PDO (PHP Data Object, hệ thống API có sẵn của PHP để kết nối đến các CSDL thông dụng), bản thân API PDO đã bảo vệ bạn trước các tấn công SQL Injection, do đó khi xử dụng Query Builder bạn không cần lo lắng xử lý dữ liệu trước khi chèn vào database.

Truy xuất dữ liệu từ database

Lấy toàn bộ các bản ghi từ một bảng

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

Sử dụng phương thức table() để trả về một instance query builder của một bảng, khi đó muốn lấy tất cả các bản ghi sử dụng phương thức get(). Phương thức get() trả về Illuminate\Support\Collection với mỗi bản ghi là một đối tượng StdClass trong PHP.

Lấy một bản ghi hoặc một cột trong bảng

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

trả về một bản ghi, còn value() trả về một cột trong bảng

$email = DB::table('users')->where('name', 'FirebirD')->value('email');

Lấy nhiều cột trong bảng

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

@foreach ($users as $id => $name)
    {{ $name }} có mã số là {{ $id }}
@endforeach

Lấy giá trị tổng hợp từ kết quả truy vấn

Laravel Query Builder hỗ trợ nhiều các phương thức tổng hợp như sau:

  • min: giá trị nhỏ nhất
  • max: giá trị lớn nhất
  • avg: giá trị trung bình
  • count: tổng số bản ghi
  • sum: lấy tổng một trường nào đó của bảng.
$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');

Các phương thức trong query builder

Select

select sử dụng để lựa chọn các cột trong một bảng:

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

distinct() sử dụng để lọc và trả về các kết quả không bị trùng lặp

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

Raw expression

Đôi khi bạn muốn có một thành phần của raw SQL query, có thể sử dụng DB::raw

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

Mệnh đề where

Các mệnh đề where đơn giản với điều kiện =, >=, <=, <>, like

$users = DB::table('users')->where('votes', '=', 100)->get();
$users = DB::table('users')
                ->where('name', 'like', '%Kiên%')
                ->get();

Thậm chí có thể truyền một mảng điều kiện vào mệnh đề where

$users = DB::table('users')->where([
    ['active', '=', '1'],
    ['vote', '>', '50'],
    ['name', 'like', '%Kiên%'],
])->get();

Điều kiện trong một câu truy vấn có thể rất phức tạp khi kết hợp các mệnh đề where khác như: orWhere, whereBetween, whereNotBetween, whereIn, whereNotIn, whereNull, whereNotNull, whereDate, whereMonth, whereDay, whereYear, whereColumn, whereExist. Các ví dụ cho từng kiểu mệnh đề where như sau:

// Ví dụ về orWhere
$users = DB::table('users')
                    ->where('vote', '>', 50)
                    ->orWhere('comment', '>', 50)
                    ->get();
// Ví dụ về whereBetween
$users = DB::table('users')
                    ->whereBetween('age', [18, 35])->get();
// Ví dụ về whereIn
$users = DB::table('users')
                    ->whereIn('id', [1, 2, 3])
                    ->get();
// Ví dụ về whereNull
$users = DB::table('users')
                    ->whereNull('vote')
                    ->get();
// Ví dụ về whereDate
$users = DB::table('users')
                ->whereDate('join_date', '2017-03-27')
                ->get();
//Ví dụ về whereDay
$users = DB::table('users')
                ->whereDay('join_date', '27')
                ->get();
// Ví dụ về whereMonth
$users = DB::table('users')
                ->whereMonth('join_date', '03')
                ->get();
//Ví dụ về whereYear
$users = DB::table('users')
                ->whereYear('join_date', '2017')
                ->get();
//Ví dụ về whereColumn
$users = DB::table('account')
                ->whereColumn([
                    ['working_balance', '=', 'last_balance'],
                    ['updated_at', '>', 'created_at']
                ])->get();
// Ví dụ về whereExist
DB::table('users')
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                      ->from('orders')
                      ->whereRaw('orders.user_id = users.id');
            })
            ->get();
// Ví dụ nhóm các điều kiện trong mệnh đề điều kiện
DB::table('users')
            ->where('name', '=', 'FirebirD')
            ->orWhere(function ($query) {
                $query->where('vote', '>', 50)
                      ->where('role', '<>', 'superadmin');
            })
            ->get();

Thao tác với kết quả trả về

Sắp xếp kết quả trả về của truy vấn bằng phương thức orderBy()

$users = DB::table('users')
                ->orderBy('vote', 'desc')
                ->get();

Lấy ngẫu nhiên một bản ghi

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

GroupBy và having:

$users = DB::table('users')
                ->groupBy('account_id')
                ->having('account_id', '>', 100)
                ->get();
Insert một hoặc nhiều bản ghi vào CSDL
// Insert một bản ghi
DB::table('users')->insert(
    ['name' => 'Nguyễn Văn A', 'email' => 'anv@gmail.com', 'votes' => 0]
);
// Insert nhiều bản ghi
DB::table('users')->insert([
    ['name' => 'Nguyễn Văn A', 'email' => 'anv@gmail.com', 'votes' => 0],
    ['name' => 'Nguyễn Văn B', 'email' => 'bnv@gmail.com', 'votes' => 0],
    ['name' => 'Nguyễn Văn C', 'email' => 'cnv@gmail.com', 'votes' => 0],
    ['name' => 'Nguyễn Văn D', 'email' => 'dnv@gmail.com', 'votes' => 0],
]);

Có những trường hợp khi insert vào CSDL bạn cần lấy lại ID bản ghi để làm tham số cho các thao tác tiếp theo, sử dụng insertGetId

$user_id = DB::table('users')->insertGetId(
    ['name' => 'Nguyễn Văn A', 'email' => 'anv@gmail.com', 'votes' => 0]
);
echo 'User vừa đăng ký có id là' . $user_id;
Update dữ liệu trong CSDL

Sử dụng phương thức update(), ví dụ:

DB::table('users')
            ->whereYear('join_date', '2017')
            ->orWhere('vote', '>', 50)
            ->update(['generate_promote_code' => 1]);
Delete dữ liệu trong CSDL
DB::table('users')
              ->where('active', '=', 0)
              ->whereYear('join_date', '2015')
              ->delete();

Để xóa toàn bộ các bản ghi trong một bảng và thiết lập ID sinh tự động về 0 sử dụng lệnh truncate()

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

Ví dụ sử dụng Laravel Query Builder

Xây dựng một số trang phục vụ các việc sau nhập thông tin sản phẩm, quản lý danh sách sản phẩm: xóa sản phẩm, sửa sản phẩm... Chúng ta cùng bắt đầu thực hiện nào!

Bước 1: Tạo bảng products trong database laravel-test đã tạo trong bài Làm việc với cơ sở dữ liệu trong Laravel.

Bảng products trong ví dụ sử dụng Query Builder trong Laravel

Bước 2: Tạo một controller tên là ProductController, sử dụng Restful Resource Controller.

c:\xampp\htdocs\laravel-test>php artisan make:controller ProductController --res
ource
Controller created successfully.

Bước 3: Thêm resource route vào routes/web.php

Route::resource('product', 'ProductController', ['only' => [
    'create', 'store', 'edit'
]]);

Bước 4: Xử lý từng hành động trên sản phẩm Bảng route tương ứng cho từng hành động như sau:

Verb URI Action Route Name
GET /product index product.index
GET /product/create create product.create
POST /product store product.store
GET /product/{product_id} show product.show
GET /product/{product_id}/edit edit product.edit
PUT/PATCH /product/{product_id} update product.update
DELETE /product/{product_id} destroy product.destroy

Tạo mới sản phẩm:

Tạo view create.blade.php trong resources/views/fontend/product

@extends('layouts.default')

@section('title', 'Tạo sản phẩm')

@section('content')
    @if(isset($success))
    <div class="alert alert-success" role="alert">{{ $success }}</div>
    @endif
    @if(isset($fail))
    <div class="alert alert-danger" role="alert">{{ $fail }}</div>
    @endif

    {!! Form::open(array('url' => '/product', 'class' => 'form-horizontal')) !!}
      <div class="form-group">
         {!! Form::label('name', 'Tên sản phẩm', array('class' => 'col-sm-3 control-label')) !!}
         <div class="col-sm-9">
            {!! Form::text('name', '', array('class' => 'form-control')) !!}
         </div>
      </div>

      <div class="form-group">
         {!! Form::label('price', 'Giá sản phẩm', array('class' => 'col-sm-3 control-label')) !!}
         <div class="col-sm-3">
            {!! Form::text('price', '', array('class' => 'form-control')) !!}
         </div>
      </div>

      <div class="form-group">
         {!! Form::label('content', 'Nội dung sản phẩm', array('class' => 'col-sm-3 control-label')) !!}
         <div class="col-sm-9">
            {!! Form::textarea('content', '', array('class' => 'form-control', 'rows' => 3)) !!}
         </div>
      </div>

      <div class="form-group">
         {!! Form::label('image_path', 'Ảnh sản phẩm', array('class' => 'col-sm-3 control-label')) !!}
         <div class="col-sm-9">
            {!! Form::text('content', '', array('class' => 'form-control')) !!}
         </div>
      </div>

      <div class="form-group">
         {!! Form::label('active', 'Active', array('class' => 'col-sm-3 control-label')) !!}
         <div class="col-sm-3">
            {!! Form::checkbox('active', '', true) !!}
         </div>
      </div>  

      <div class="form-group">
         <div class="col-sm-offset-2 col-sm-10">
            {!! Form::submit('Tạo sản phẩm', array('class' => 'btn btn-success')) !!}
         </div>
      </div>
   {!! Form::close() !!}
@endsection

Thêm nội dung phương thức create trong ProductController

/**
 * Show the form for creating a new resource.
 *
 * @return \Illuminate\Http\Response
 */
public function create()
{
    return view('fontend.product.create');
}

Tiếp đến chúng ta xử lý insert sản phẩm vào CSDL, nó sẽ nằm trong phương thức store của ProductController (xem bảng các route của product)

/**
 * Store a newly created resource in storage.
 *
 * @param  \Illuminate\Http\Request  $request
 * @return \Illuminate\Http\Response
 */
public function store(Request $request)
{
    $validator = Validator::make($request->all(), [
        'name'       => 'required|max:255',
        'price'      => 'required|number',
        'content'    => 'required',
        'image_path' => 'required'
    ]);

    if ($validator->fails()) {
        return redirect('product/create')
                ->withErrors($validator)
                ->withInput();
    } else {
        // Lưu thông tin vào database, phần này sẽ giới thiệu ở bài về database
        $active = $request->has('active')? 1 : 0;
        $product_id = DB::table('product')->insertGetId(
            'name'       => $request->input('name'),
            'price'      => $request->input('price'),
            'content'    => $request->input('content'),
            'image_path' => $request->input('image_path'),
            'active'     => $active
            );
        return redirect('product/create')
                ->with('message', 'Sản phẩm được tạo thành công với ID: ' . $product_id);
    }
}

Để nhập sản phẩm ta vào đường dẫn http://laravel.dev/product/create

Màn hình tạo sản phẩm trong ví dụ sử dụng query builder trong Laravel

Danh sách sản phẩm:

Danh sách sản phẩm sẽ được xử lý trong phương thức index của ProductController, chúng ta thêm nội dung cho phương thức này như sau:

/**
 * Display a listing of the resource.
 *
 * @return \Illuminate\Http\Response
 */
public function index()
{
    $products = DB::table('products')->get();
    return view('fontend.product.list')->with($products);
}

Tiếp đến chúng ta tạo một view để hiển thị danh sách sản phẩm tên là list.blade.php nằm trong thư mục resources/views/fontend/product

@extends('layouts.default')

@section('title', 'Danh sách sản phẩm')

@section('content')
    <table class="table table-bordered">
        <tr class="success">
            <th>ID</th>
            <th>Tên sản phẩm</th>
            <th>Giá sản phẩm</th>
            <th>Nội dung</th>
            <th>Ảnh sản phẩm</th>
            <th>Đăng bán</th>
            <th>Action</th>
        </tr>
        @foreach($products as $p)
        <tr>
            <td>{{ $p->id }}</td>
            <td>{{ $p->name }}</td>
            <td class="text-right">{{ number_format($p->price) }}</td>
            <td>{{ $p->content }}</td>
            <td>
                <img src="{{ Asset($p->image_path) }}" alt="{{ $p->name }}" width="120" height="120">
            </td>
            <td>
                @if($p->active)
                    <span class="text-success glyphicon glyphicon-ok"></span>
                @else
                    <span class="text-danger glyphicon glyphicon-remove"></span>
                @endif
            </td>
            <td>
                <a href="{{ '/product/' . $p->id . '/edit'}}"><span class="glyphicon glyphicon-pencil">Edit</span></a>
                <a href="{{ '/product/' . $p->id }}"><span class="glyphicon glyphicon-trash">Delete</span></a>
            </td>
        </tr>
        @endforeach
    </table>
@endsection

Kết quả khi chạy http://laravel.dev/product ta có danh sách các sản phẩm

Danh sách sản phẩm trong ví dụ sử dụng query builder trong Laravel

Chỉnh sửa sản phẩm

Chúng ta sử dụng phương thức edit() trong ProductController để chỉnh sửa sản phẩm, nội dung phương thức này như sau:

 /**
 * Show the form for editing the specified resource.
 *
 * @param  int  $id
 * @return \Illuminate\Http\Response
 */
public function edit($id)
{
    $product = DB::table('products')->find($id);
    return view('fontend.product.edit')->with(compact('product'));
}

Tạo một view để chỉnh sửa sản phẩm resources/views/fontend/product/edit.blade.php với nội dung như sau:

@extends('layouts.default')

@section('title', 'Chỉnh sửa sản phẩm')

@section('content')
    @if(isset($success))
    <div class="alert alert-success" role="alert">{{ $success }}</div>
    @endif
    @if (count($errors) > 0)
        <div class="alert alert-danger">
            <ul>
                @foreach ($errors->all() as $error)
                    <li>{{ $error }}</li>
                @endforeach
            </ul>
        </div>
    @endif

    {!! Form::open(array('url' => '/product/' . $product->id, 'class' => 'form-horizontal', 'method' => 'put')) !!}
      <div class="form-group">
         {!! Form::label('name', 'Tên sản phẩm', array('class' => 'col-sm-3 control-label')) !!}
         <div class="col-sm-9">
            {!! Form::text('name', $product->name, array('class' => 'form-control')) !!}
         </div>
      </div>

      <div class="form-group">
         {!! Form::label('price', 'Giá sản phẩm', array('class' => 'col-sm-3 control-label')) !!}
         <div class="col-sm-3">
            {!! Form::text('price', $product->price, array('class' => 'form-control')) !!}
         </div>
      </div>

      <div class="form-group">
         {!! Form::label('content', 'Nội dung sản phẩm', array('class' => 'col-sm-3 control-label')) !!}
         <div class="col-sm-9">
            {!! Form::textarea('content', $product->content, array('class' => 'form-control', 'rows' => 3)) !!}
         </div>
      </div>

      <div class="form-group">
         {!! Form::label('image_path', 'Ảnh sản phẩm', array('class' => 'col-sm-3 control-label')) !!}
         <div class="col-sm-9">
            {!! Form::text('image_path', $product->image_path, array('class' => 'form-control')) !!}
         </div>
      </div>

      <div class="form-group">
         {!! Form::label('active', 'Active', array('class' => 'col-sm-3 control-label')) !!}
         <div class="col-sm-3">
            {!! Form::checkbox('active', $product->active, true) !!}
         </div>
      </div>  

      <div class="form-group">
         <div class="col-sm-offset-2 col-sm-10">
            {!! Form::submit('Chỉnh sửa sản phẩm', array('class' => 'btn btn-success')) !!}
         </div>
      </div>
   {!! Form::close() !!}
@endsection

Phương thức update() của ProductController đảm nhận phần cập nhật nội dung sản phẩm, thêm code vào phương thức này như sau:

/**
 * Update the specified resource in storage.
 *
 * @param  \Illuminate\Http\Request  $request
 * @param  int  $id
 * @return \Illuminate\Http\Response
 */
public function update(Request $request, $id)
{
    $active = $request->has('active')? 1 : 0;
    $updated = DB::table('products')
        ->where('id', '=', $id)
        ->update([
            'name'       => $request->input('name'),
            'price'      => $request->input('price'),
            'content'    => $request->input('content'),
            'image_path' => $request->input('image_path'),
            'active'     => $active,
            'updated_at' => \Carbon\Carbon::now()
            ]);
    return Redirect::back()
        ->with('message', 'Cập nhật sản phẩm thành công')
        ->withInput(); 
}

OK, giờ chúng ta click vào Edit sản phẩm bất kỳ trong danh sách sản phẩm nó sẽ xuất hiện cửa sổ chỉnh sửa sản phẩm

Màn hình chỉnh sửa sản phẩm trong ví dụ sử dụng query builder trong laravel

Thay đổi thông tin và click Chỉnh sửa sản phẩm, sau đó vào lại trang danh sách sản phẩm http://laravel.dev/product chúng ta sẽ thấy nội dung đã thay đổi. Xóa một sản phẩm Thông thường các sản phẩm khi đã nhập vào hệ thống chúng ta sẽ không xóa đi mà chỉ cần không active nó lên là ok, do vậy phần này mình bỏ qua nhé, với lại cũng hơi lười tí.

Lời kết

Laravel Query Builder giúp chúng ta xây dựng ứng dụng thật nhanh chóng cho dù các câu truy vấn có phức tạp đến đâu chúng ta cũng xử lý rất dễ dàng. Trong ví dụ phần ảnh sản phẩm tôi tạm thời để theo kiểu copy bằng tay đường dẫn ảnh sản phẩm. Trong thời gian tới, khi nào rảnh tôi sẽ hướng dẫn bạn tích hợp CKEditor và CKFinder vào hệ thống giúp đưa vào nội dung phong phú vào và quản lý lựa chọn tài nguyên như ảnh, file dễ dàng hơn.


CÁC BÀI VIẾT KHÁC

FirebirD

Đam mê Toán học, Lập trình. Sở thích chia sẻ kiến thức, Phim hài, Bóng đá, Cà phê sáng với bạn bè.

Làm việc với cơ sở dữ liệu trong Laravel

Phân trang kết quả với Laravel Pagination

12 Bình luận trong "Xây dựng truy vấn bằng Laravel Query Builder"

  1. PHPKungfu

    2 years ago

    Phản hồi
    Anh em cho hỏi chút, trong restful resource controller phần delete phải dùng method là DELETE vậy phải dùng form chứ không dùng dạng thẻ được nhỉ?
    1. theo như mình biết thì thẻ chỉ hỗ trợ mỗi phương thức GET, muốn sử dụng các phương thức khác khi gửi dữ liệu bằng thẻ phải dùng các javascript framework như jQuery chẳng hạn. Bạn xem ví dụ sau đây bằng jQuery: $(document).ready(function() { /* Quản lý click trên thẻ a cho restful DELETE requests Ví dụ: Delete restful.init($('.rest-delete')); */ var restful = { init: function(elem) { elem.on('click', function(e) { self=$(this); e.preventDefault(); if(confirm('Are you sure?')) { $.ajax({ url: self.attr('href'), method: 'DELETE', success: function(data) { self.parent('li').remove(); }, error: function(data) { alert("Error while deleting."); console.log(data); } }); }; }) }, } restful.init($('.rest-delete')); });
  2. Bảo

    2 years ago

    Phản hồi
    Ad ơi có thể giúp e triển khai câu lệnh sql này sang query builder được ko , e chạy trong mysql thì ra kết quả còn e cover sang query builder không được . Select * from ( Select *,(select count(1) from tb_episode where tb_episode.manga_id = tb_manga.id ) as sochap from tb_manga LIMIT 10 ) as X Order by sochap DESC
  3. Anh

    1 year ago

    Phản hồi
    Ad ơi cho em hỏi. Em đã làm theo các bước nhưng sao em vẫn bị mắc lỗi này ạ :( em fix mãi không được. http://prntscr.com/h53bug
    1. FirebirD

      1 year ago

      Phản hồi
      Tham số truyền vào cho insertGetId() là một mảng, chắc do code của mình đưa vào bài bị thiếu, bạn thêm dấu [] để đánh dấu đây là mảng. $product_id = DB::table('product')->insertGetId([ 'name' => $request->input('name'), 'price' => $request->input('price'), 'content' => $request->input('content'), 'image_path' => $request->input('image_path'), 'active' => $active ]); Bạn thử xem được không nhé?
      1. Anh

        1 year ago

        Phản hồi
        Cảm ơn ad. Được rồi ạ :)
      2. Anh

        1 year ago

        Phản hồi
        Nhưng khi nhấn tạo sản phầm thì laiuj xuất hiện thêm lỗi này :( http://prntscr.com/h5kz27
  4. Pet

    1 year ago

    Phản hồi
    "Undefined variable: product (View: D:\IT\XAMPP\htdocs\SEP_12052018\resources\views\List_Product.blade.php)" em bị lỗi này chỗ lấy dữ liệu từ dưới mysql lên view nhưng khi chỉnh lại trong ListProduct.blade.php chỗ @foreach($product) {{$row->TENLOAISP}} {{$row->TENSP}} {{$row->SOLUONG}} {{ number_format($row->GIA) }} {{ number_format($row->GIACU) }} {{$row->DONVI}} {{$row->TRANGTHAI}} GCN)}}" width="120" , height="120"> HINH)}}" width="120" , height="120"> @endforeach thì nó xuất hiện lỗi này : "Undefined offset: 1" trên mảng hình protected function compileForeach($expression) { preg_match('/\( *(.*) +as *(.*)\)$/is', $expression, $matches); $iteratee = trim($matches[1]); $iteration = trim($matches[2]); $initLoop = "\$__currentLoopData = {$iteratee}; \$__env->addLoop(\$__currentLoopData);"; $iterateLoop = '$__env->incrementLoopIndices(); $loop = $__env->getLastLoop();'; return ""; } /** * Compile the break statements into valid PHP. * * @param string $expression * @return string Vậy thì em nên sửa sao ạ :((
    1. ToanMTE

      1 year ago

      Phản hồi
      Cú pháp @foreach trong Laravel của bạn bị sai hoặc do bạn gõ thiếu trong phần bình luận? @foreach ($users as $user) This is user {{ $user->id }} @endforeach Nếu bạn gõ thiếu thì bạn có thể tiếp tục debug bằng cách in các biến liên quan, tham khảo https://allaravel.com/laravel-tutorials/cac-cong-cu-huu-ich-cho-debug-code-va-kiem-thu-trong-laravel/
      1. Pet

        1 year ago

        Phản hồi
        e cảm ơn
  5. Trọng

    9 months ago

    Phản hồi
    Hi bạ, làm thế nào để join từ bảng A sang bảng B kết quả trả về là object chứa các trường của bảng A và chứa luôn 1 object con có các thuộc tính là các trường của bảng B. (Định dạng giống relationship của ORM)? thank.
    1. FirebirD

      8 months ago

      Phản hồi
      Bạn có thể sử dụng Accessor, tham khảo bài viết https://allaravel.com/laravel-tutorials/laravel-eloquent-orm-phan-3-xu-ly-du-lieu-dau-ra/.

Thêm bình luận