jQuery Datatables and Laravel 6.x Pagination

jQuery DataTables is a table component that is powerful for data management. These plugins provide searching, sorting, pagination, filter per column, and many more. But we have some problems combining jQuery Datatables and Laravel framework.

We separate applications (Rest API and User Interface). For Rest API we use Laravel Framework and for User Interface use Laravel too, but we only Blade component and Guzzle as HTTP Client Library.

We now Laravel has good packages for handle it, as far I know Yajra’s good and incredible packages (you can see Yajra’s package on Github). But using Yajra’s library was not possible at that time, because Yajra was using Eloquent and can’t use HTTP Client (like Guzzle, UniRest, etc). Then we do research to deal with this problem and got results as we would discuss in this post.

On these tutorials, we use an empty Laravel 6.x project with Bootstrap 3, jQuery Datatables plugins. Our goal in this tutorial is to Display User Lists in Datatables (Searching and Sorting also). Ok let’s start the codings.

Integrated jQuery Datatables and Laravel Framework

First Step

Setup the database connection on your Laravel .env. like this

After that, run database migration and create 100 dummy records using the Faker library, Ok open your Terminal then run step by step this command

Run Laravel migration by typing a command

Run Laravel tinker by typing a command

Results of Laravel Tinker

Ok, the tables and data dummy creation are already completed, The next step is to make a new controller using a command line.

Step Two

Create a new controller, in this tutorial we created a UserController

Open the UserController and add an index method.

The method above is very simple, which functions are only to display the index page. After you create a method, the next step is to register the index method to the Laravel’s routing.

Step Three

Open and edit the file routes/web.php, and add this route

Step Four

Make a View in resources/views/user folder and name it index.blade.php. After you created it we need to create an HTML table and a jQuery Datatables scripts on it. Use the snippet code below for the table.

The above code has no special needs, it’s just that you need to make sure the table id is the same as id on the javascript code. After we create a table, the next step is to create a jQuery Datatables codes, the code is like this

On line 104 in the code above, we add a URL to retrieve data from the UserController, with the URL prefix being ‘/getDatatable’. The full code is as follows, *noted we use the Laravel 5.2.x template because it still uses purely a blade (not vue.js template)

in the code above I added Bootstrap 3 and jQuery Datatables plugins. if it turns out you already use Bootstrap 4, you can change the script and style at cdn.datatables.net, then you choose the styling options based on your needs. The code for the View is now finished, we can proceed to the next step.

Step Five (Final Steps)

In the UserController we add a method named getDatatable, what is the function? well, its function is to get data from the users table in your database.

Is the code length too long? Yup, a little long is okay as long as it has a powerful function. we discussed the code above step by step, but if you want to take action please scroll to the bottom. Let’s start codes explain

The code blocks above are the default parameters sent by the jQuery Datatables :

$start is the position from which the data will be displayed (default parameter from Datatables).

$length is how much data will be displayed (default parameter from Datatables).

$page is a calculation prefix for Laravel’s pagination, it is also used to provide a default value if the parameter is not sent by the Datatables.

$limit is a calculation prefix for Laravel’s pagination, it is also used to provide a default value if the parameter is not sent by the Datatables.

$columnName is the name of the column in the Datatables, this value is usually used if you are searching based on the column name provided.

$columnSortOrder variable is used to sort either Ascending or Descending based on the selected column.

$searchValue variable is used to find data.

Okay, we scroll down a little, there is a code

The code is used so that the Datatables can search for data in the column name or also the email column in the users table. In the code, there is a condition 1 = 1, it is a hack so that if the Datatables is not sending the search parameter our controller will not display an error. So by default Laravel will generate SQL queries like this :

and if we do a search data, Laravel will generate an SQL query like this:

Lovely hack right? continue scrolling down again, there is a code

$countAll is the variable needed by the Datatables to count the total number of records in the users table.

$paginate is a variable to paginate from Laravel, this code block does not need to be changed (maybe only the SELECT (‘*’) that need to be changed to optimize the query time results)

The code above works for displaying some columns in the Datatables, in these tutorials we will display 4 columns

• no
• id
• name
• email

and the last is the $response variable, the code is the output rules required by the jquery Datatables plugin, so we have to follow it and don’t need to change anything.

And finally, onto the final stage. Curious as to what the results are? please open your browser and navigate to the URL

HTTP://{{HOST_ANDA}}/datatable

Do you see any results? if so, do a data Searching and also Sorting the Datatables column, does it work? if it is CONGRATULATIONS! and I need to inform you about the code, the sample code above is production-ready, so it is safe to use the code above if you have a project or product.

This is the end of the first tutorial. too long huh? but satisfied with the results right? you don’t need plugins and so on to do this. honestly, I think it’s a little hard to fix people’s code if there’s a bug in someone’s package. so if you can do it by yourself why use someone else’s package? See you in the next tutorials!

jQuery Datatable and Laravel 6.x Source Code