Laravel & DataTable - Passing Data

0. Setting up DataTable in Laravel
1. Passing Data
2. Filtering / Ordering
3. Export to CSV, Excel or PDF

With DataTable setup in Laravel, we can retrieve data from our data source and initialise DataTable and let it do its magic.

Dirty and Quick Way - Not recommended

A very simple example of retrieving all the books from our Book model and dumping them all to to our view.

// BooksController
public function index()
{
    $books = \App\Book::get();
    
    return view('books.index', compact('books'));
}

Obviously it's ineffective to do so as you're retrieving EVERYTHING from the database table and store them in your memory. As your database grows, definitely you will encounter the out of memory fatal error sooner or later.

Having 5,000 records seeded into the Book table, it gets sluggish when we are using this method to load the data.

It actually loops through all 5k records and render them on screen, then only DataTable is being initialised - bad for performance and user experience.

The Correct Way, Path to Happiness - Recommended

Datatable has a built in way to load data by using AJAX. With this method, the web page will be loaded, then only it fires an AJAX request to the server to ask for data. Every subsequent actions (paginate, search, sort) will actually retrieve only the amount of data needed to fill up the table by triggering an AJAX request.

Let's go through how to achieve this in a few simple steps.

1. A 3rd party package - YajraBox Laravel DataTable will be used to easily convert Laravel Eloquent to a format which conforms to DataTable's. Install the package by executing this in your terminal.

composer require yajra/laravel-datatables-oracle:^8.0

2. Add a new route in routes/web.php for the AJAX request.

Route::get('books/query', 'BookingController@query')->name('books.query');

3. Add the method in your BookController.

use Yajra\DataTables\DataTables;

//...

public function query()
{
      return Datatables::of(\App\Book::query())
  		   ->make(true);
}

4. Let's update our blade file to instruct DataTable to retrieve data from the server side by setting the passing serverSide: true and an ajax data source. We set the columns array to specify which key in the objects to be used in sequence.

   In this case, we only have 1 column in our table named name.

$(document).ready(function () {
        $('#book-table').DataTable({
            processing: true,
            language: {
                processing: '<span>Processing</span>',
            },
            serverSide: true,
            ajax: '{{ route('books.query') }}',
            columns: [
                { data: 'name' },
            ],
        });
    });

Links to the complete controller file & blade file.