Tutorial Laravel

Join, Search, Filter Laravel 9 + Bootstrap5 + MySQL

/ by | 9 Komentar pada Join, Search, Filter Laravel 9 + Bootstrap5 + MySQL

Join, Search, Filter Laravel 9

Join, Search, Filter Laravel 9 – Tutorial Join, Search, Filter pada Laravel 9 dengan Bootstrap 5 dan database MySQL. Kasus kali ini menggunakan Nortwind Database.

1. Membuat Project Laravel

Pastikan Anda sudah menginstall composer, kemudian jalankan perintah berikut di command prompt.

composer create-project laravel/laravel laravel-filter

2. Mengatur Database MySQL

Langkah kedua adalah membuat database dengan nama laravel_filter di http://localhost/phpmyadmin jika Anda menginstall XAMPP untuk server MySQL.

Sesuaikan pengaturan koneksi mysql di project laravel pada file .env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel_filter
DB_USERNAME=root
DB_PASSWORD=

Sesuaikan DB_HOST, DB_PORT, DB_DATABASE, DB_USERNAME, dan DB_PASSWORD sesuai server MySQL.

3. Import Database

Pada tutorial ini sudah menyediakan data dalam bentuk sql, silakan Anda impor ke phpmyadmin. Database terdiri dari 5 tabel yaitu categories, cutomers, order_details, orders, dan products. Anda juga bisa membuat data dummy sendiri dengan migrasi, factory, dan faker seperti pada tutorial Crud Laravel 9. Download database laravel_filter.sql.

4. Membuat Model

Buat model Category dan OrderDetail menggunakan perintah berikut.

php artisan make:model Category
php artisan make:model OrderDetail

5. Membuat Controller OrderDetail

Gunakan perintah berikut untuk membuat controller.

php artisan make:controller OrderDetailController --model=OrderDetail

Sesuaikan app/controllers/OrderDetailController.php sebagai berikut.

<?php

namespace App\Http\Controllers;

use App\Models\Category;
use App\Models\OrderDetail;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class OrderDetailController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index(Request $request)
    {
        $data['q'] = $request->query('q');
        $data['category_id'] = $request->query('category_id');
        $data['start'] = $request->query('start');
        $data['end'] = $request->query('end');
        $data['categories'] = Category::all();
        $data['operators'] = [
            '=' => 'equal to',
            '<>' => 'not equal to',
            '>' => 'greater than',
            '>=' => 'greater than or equal to',
            '<' => 'less than',
            '<=' => 'less than or equal to',
            'between' => 'between',
        ];
        $data['total_operator'] = $request->get('total_operator');
        $data['total_value'] = $request->get('total_value');
        $data['total_value_end'] = $request->get('total_value_end');

        $query = OrderDetail::select('order_details.*', 'orders.*', 'customers.*', 'categories.*', 'products.*', DB::raw('quantity * price AS total'))
            ->join('products', 'products.product_id', '=', 'order_details.product_id')
            ->join('orders', 'orders.order_id', '=', 'order_details.order_id')
            ->join('customers', 'customers.customer_id', '=', 'orders.customer_id')
            ->join('categories', 'categories.category_id', '=', 'products.category_id')
            ->where(function ($query) use ($data) {
                $query->where('product_name', 'like', '%' . $data['q'] . '%');
                $query->orWhere('customer_name', 'like', '%' . $data['q'] . '%');
                $query->orWhere('category_name', 'like', '%' . $data['q'] . '%');
            });

        if ($data['start'])
            $query->whereDate('order_date', '>=', $data['start']);
        if ($data['end'])
            $query->whereDate('order_date', '<=', $data['end']);
        if ($data['category_id'])
            $query->where('categories.category_id', $data['category_id']);
        if ($data['total_operator']) {
            if ($data['total_operator'] == 'between')
                $query->whereRaw('quantity * price between ? AND ?', [$data['total_value'], $data['total_value_end']]);
            else
                $query->whereRaw('quantity * price ' . $data['total_operator'] . ' ? ', $data['total_value']);
        }

        $data['order_details'] = $query->paginate(15)->withQueryString();
        return view('order_detail.index', $data);
    }

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

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
    }

    /**
     * Display the specified resource.
     *
     * @param  \App\Models\OrderDetail  $orderDetail
     * @return \Illuminate\Http\Response
     */
    public function show(OrderDetail $orderDetail)
    {
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  \App\Models\OrderDetail  $orderDetail
     * @return \Illuminate\Http\Response
     */
    public function edit(OrderDetail $orderDetail)
    {
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  \App\Models\OrderDetail  $orderDetail
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, OrderDetail $orderDetail)
    {
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  \App\Models\OrderDetail  $orderDetail
     * @return \Illuminate\Http\Response
     */
    public function destroy(OrderDetail $orderDetail)
    {
    }
}
  • Perintah $request->query('...') berfungsi untuk mengambil nilai filter dan pencarian yang Anda masukkan.
  • Perintah ->where(function ($query) use ($data) berfungsi untuk mengelompokkan perintah pencarian dengan OR.

6. Menambahkan Route order_detail

Tambahkan route untuk crud user di routes/web.php bertipe resource seperti berikut.

<?php

use App\Http\Controllers\OrderDetailController;
use Illuminate\Support\Facades\Route;

/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/

Route::get('/', function () {
    return view('welcome');
});

Route::resource('order_detail', OrderDetailController::class);

7. Membuat View

Buat view untuk menampilkan data order_detail di direktori resources/views/order_detail/index.blade.php.

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Filter and Join Laravel</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
</head>

<body>
    <div class="container-fluid">
        <h1>Order Details</h1>
        <div class="card">
            <div class="card-header">
                <form class="row row-cols-lg-auto g-1">
                    <div class="col">
                        <select class="form-select" name="category_id">
                            <option value="">All Category</option>
                            @foreach($categories as $category)
                            @if($category->category_id==$category_id)
                            <option value="{{ $category->category_id }}" selected>{{ $category->category_name }}</option>
                            @else
                            <option value="{{ $category->category_id }}">{{ $category->category_name }}</option>
                            @endif
                            @endforeach
                        </select>
                    </div>
                    <div class=" col">
                        <div class="input-group">
                            <input class="form-control" type="date" name="start" value="{{ $start }}" />
                            <input class="form-control" type="date" name="end" value="{{ $end }}" />
                        </div>
                    </div>
                    <div class="col">
                        <div class="input-group">
                            <select class="form-select" name="total_operator" onchange="hide_total_value_end()">
                                <option value="">All Total</option>
                                @foreach($operators as $key => $val)
                                @if($key==$total_operator)
                                <option value="{{ $key }}" selected>Total {{ $val }}</option>
                                @else
                                <option value="{{ $key }}">Total {{ $val }}</option>
                                @endif
                                @endforeach
                            </select>
                            <input class="form-control" type="text" name="total_value" value="{{ $total_value }}" size="4" />
                            <input class="form-control" type="text" name="total_value_end" value="{{ $total_value_end }}" size="4" />
                        </div>
                    </div>
                    <div class=" col">
                        <input class="form-control" type="text" name="q" value="{{ $q }}" placeholder="Search here..." />
                    </div>
                    <div class="col">
                        <button class="btn btn-success">Search</button>
                    </div>
                </form>
            </div>
            <div class="card-body p-0">
                <table class="table table-bordered table-striped table-hover table-sm m-0">
                    <thead>
                        <tr>
                            <th>#</th>
                            <th>OrderID</th>
                            <th>OrderDate</th>
                            <th>CustomerID</th>
                            <th>CustomerName</th>
                            <th>ProductID</th>
                            <th>ProductName</th>
                            <th>CategoryID</th>
                            <th>CategoryName</th>
                            <th>Quantity</th>
                            <th>Price</th>
                            <th>Total</th>
                        </tr>
                    </thead>
                    <?php
                    $i = $order_details->firstItem();
                    ?>
                    @foreach($order_details as $order_detail)
                    <tr>
                        <td>{{ $i++ }}</td>
                        <td>{{ $order_detail->order_id }}</td>
                        <td>{{ date('M d Y', strtotime($order_detail->order_date)) }}</td>
                        <td>{{ $order_detail->customer_id }}</td>
                        <td>{{ $order_detail->customer_name }}</td>
                        <td>{{ $order_detail->product_id }}</td>
                        <td>{{ $order_detail->product_name }}</td>
                        <td>{{ $order_detail->category_id }}</td>
                        <td>{{ $order_detail->category_name }}</td>
                        <td>{{ $order_detail->quantity }}</td>
                        <td>{{ $order_detail->price }}</td>
                        <td>{{ $order_detail->total }}</td>
                    </tr>
                    @endforeach
                </table>
            </div>
            @if($order_details->hasPages())
            <div class="card-footer">
                {{ $order_details->links() }}
            </div>
            @endif
        </div>
    </div>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
    <script>
        $(function() {
            hide_total_value_end();
        })

        function hide_total_value_end() {
            if ($('select[name="total_operator"]').val() == 'between')
                $('input[name="total_value_end"]').show();
            else
                $('input[name="total_value_end"]').hide();
        }
    </script>
</body>

</html>

8. Mengatur Template Filter

Atur agar template filter sesuai dengan bootstrap 5 di app/providers/AppServiceProvider.php sebagai berikut.

<?php

namespace App\Providers;

use Illuminate\Pagination\Paginator;
use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
    }

    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        Paginator::useBootstrapFive();
    }
}

9. Menjalankan Project Join, Search, Filter Laravel 9

Langkah terakhir adalah menjalankan project. Silahkan gunakan perintah berikut untuk mengaktifkan server laravel.

php artisan serve

Ketikkan alamat sesuai server diikuti dengan /customer di browser (http://127.0.0.1:8000/order_detail). Hasilnya seperti berikut.

Filter Kategori
Filter Tanggal Awal dan Akhir

Masukkan tanggal awal dan akhir, sehingga data yang muncul antara kedua tanggal yang Anda masukkan.

Pencarian Berdasarkan Nama Customer

Pencarian bisa Anda lakukan berdasarkan Nama customer, nama kategori, maupun nama produk, sehingga lebih mudah menem

Filter Total dengan Between

Filter total antara lain:

  • sama dengan
  • lebih besar
  • lebih besar sama dengan
  • lebih kecil
  • lebih kecil sama dengan
  • diantara x dan y
Tanpa Filter dan Pencarian

Itulah tutorial membuat Advance Filter Laravel 9. Jika ada yang ditanyakan, silahkan berkomentar.

9 Comments on “Join, Search, Filter Laravel 9 + Bootstrap5 + MySQL”

  • Apayah

    says:

    mantaps bang

    • admin

      says:

      Semoga membantu 🙂

  • Apit

    says:

    Hai pak,
    sudah mencoba tutorial dari atas sampe bawah ada error
    Error
    Call to undefined method Illuminate\Pagination\Paginator::useBootstrapFive()
    http://127.0.0.1:8000/order_detail

    Mohon bantuannya

    • admin

      says:

      Tambahkan di bagian atas “use Illuminate\Pagination\Paginator;”

      Kalau menggunakan Extension di Visual Studio Code, pasti otomatis ditambahkan ketika mengetik “Paginator::useBootstrapFive();”

      • Roby Sukmana

        says:

        mas ijin bertanya, setelah saya menambahkan filter. datanya sudah muncul dan sesuai ..
        misalnya data hasil pencaraian lebih dari pagination yang kita tentukan dan menekan halaman maka akan hilang data yang terfilter tadi

        • admin

          says:

          Pastikan sudah mengisi withQueryString kak $query->paginate(15)->withQueryString()

  • Dudung Ruswan

    says:

    Terima kasih, semoga menjadi penambah amal kebaikan.

  • fea one

    says:

    liatin modelnya kak

    • admin

      says:

      Tidak ada yang di ubah di bagian model, masih standarnya.

Leave a Comment

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

To top