Create an API for DataTables with Laravel
Tags: laravel, datatables
Published: 2024-06-21 03:29:35

---
title: Create an API for DataTables with Laravel
published: true
date: 2024-06-18 09:13:00 UTC
tags: Laravel,DataTables
canonical_url: https://blog.stackpuz.com/create-an-api-for-datatables-with-laravel/
---

![Laravel API for DataTables](https://blog.stackpuz.com/media/posts/3/cover.jpg)

[DataTables](https://datatables.net/) is a popular jQuery plugin that offers features like pagination, searching, and sorting, making it easy to handle large datasets. This article will show you how to create a Laravel API to work with the DataTables. What are the parameters that DataTables sends to our API and the requirements of the data that DataTables needs.

To deal with DataTables you need to know the information that DataTables will send to the API through the query string.

```ini
draw = 1
columns[0][data] = id
columns[0][name] = 
columns[0][searchable] = true
columns[0][orderable] = true
columns[0][search][value] = 
columns[0][search][regex] = false
columns[1][data] = name
columns[1][name] = 
columns[1][searchable] = true
columns[1][orderable] = true
columns[1][search][value] = 
columns[1][search][regex] = false
columns[2][data] = price
columns[2][name] = 
columns[2][searchable] = true
columns[2][orderable] = true
columns[2][search][value] = 
columns[2][search][regex] = false
order[0][column] = 0
order[0][dir] = asc
order[0][name] = 
start = 0
length = 10
search[value] = 
search[regex] = false
```

- `draw` the request ID that is used to synchronize between the client and server.
- `columns[x][data]` the column's field name that we define on the client-side.
- `order[0]` the sorting information.
- `start` the start index of the record. We do not use it, because Laravel pagination use a page index instead. We will write some JavaScript to generate this page index later.
- `length` the length per page (page size).
- `search[value]` the search value information.

The DataTables expected data will require these information.

- `draw` DataTables sends this ID to us, and we just send it back.
- `recordsTotal` Total records number before filtering.
- `recordsFiltered` Total records number after filtering.
- `data` The records data.

## Prerequisites 

- Composer
- PHP 8.2
- MySQL

## Setup project 

Create a new laravel project.

```batchfile
composer create-project laravel/laravel laravel_api 11.0.3
```

Create a testing database named "example" and run the [database.sql](https://github.com/stackpuz/Example-DataTables-Laravel-11/blob/main/database.sql) file to import the table and data.

## Project structure 

```
├─ .env
├─ app
│  ├─ Http
│  │  └─ Controllers
│  │     └─ ProductController.php
│  └─ Models
│     └─ Product.php
├─ bootstrap
│  └─ app.php
├─ resources
│  └─ views
│     └─ index.php
└─ routes
   ├─ api.php
   └─ web.php
```

\*This project structure will show only files and folders that we intend to create or modify.

## Project files 

### .env 

This file is the Laravel configuration file and we use it to keep the database connection information.

```ini
DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=example
DB_USERNAME=root
DB_PASSWORD=

SESSION_DRIVER=file
```

We also set `SESSION_DRIVER=file` to change the session driver from database to file.

### app.php 

This file is the Laravel application configuration file, and we only added the API routing file here.

```php
<?php

use Illuminate\Foundation\Application;
use Illuminate\Foundation\Configuration\Exceptions;
use Illuminate\Foundation\Configuration\Middleware;

return Application::configure(basePath: dirname( __DIR__ ))
    ->withRouting(
        web: __DIR__.'/../routes/web.php',
        api: __DIR__.'/../routes/api.php',
        commands: __DIR__.'/../routes/console.php',
        health: '/up',
    )
    ->withMiddleware(function (Middleware $middleware) {
        //
    })
    ->withExceptions(function (Exceptions $exceptions) {
        //
    })->create();

```

### web.php 

This file defines the route URL for the Laravel web application. We just changed the default file from welcome.php to index.php.

```php
<?php

use Illuminate\Support\Facades\Route;

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

```

### api.php 

This file defines the route URL for the Laravel API. We define our API route here.

```php
<?php

use App\Http\Controllers\ProductController;

Route::get('/products', [ProductController::class, 'index']);
```

### Product.php 

This file defines the model information that maps to our database table named "Product".

```php
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    protected $table = 'Product';
    protected $primaryKey = 'id';
}
```

\*To keep the code simple, we define only a few pieces of information here. This is enough for our API.

### ProductController.php 

This file is used to handle incoming requests from DataTables and produce the appropriate data for them.

```php
<?php

namespace App\Http\Controllers;

use App\Models\Product;

class ProductController {

    public function index()
    {
        $size = request()->input('length') ?? 10;
        $order = request()->input('order') ? request()->input('columns')[request()->input('order')[0]['column']]['data'] : 'id';
        $direction = request()->input('order') ? request()->input('order')[0]['dir'] : 'asc';
        $search = request()->input('search')['value'];
        $query = Product::query()
            ->select('id', 'name', 'price')
            ->orderBy($order, $direction);
        $recordsTotal = $query->count();
        if ($search) {
            $query->where('name', 'like', "%$search%");
        }
        $paginate = $query->paginate($size);
        return ['draw' => request()->input('draw'), 'recordsTotal' => $recordsTotal, 'recordsFiltered' => $paginate->total(), 'data' => $paginate->items()];
    }
}
```

- We utilize the query string to get `$size, $order, $direction, $search` and create the paginated data by using the `paginate($size)` method.
- We return all DataTables required information including: `draw,  recordsTotal, recordsFiltered, data` as object.

### index.php 

This file will be used to define the DataTables HTML and JavaScript to consume our API.

```html
<!DOCTYPE html>
<head>
    <link rel="stylesheet" href="https://cdn.datatables.net/2.0.7/css/dataTables.dataTables.min.css">
</head>
<body>
    <table id="table" class="display">
        <thead>
            <td>id</td>
            <th>name</th>
            <th>price</th>
        </thead>
    </table>
    <script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
    <script src="https://cdn.datatables.net/2.0.7/js/dataTables.min.js"></script>
    <script>
        var dataTable = new DataTable('#table', {
            ajax: {
                url: '/api/products',
                data: {
                    page: () => (dataTable && dataTable.page() + 1) || 1
                }
            },
            processing: true,
            serverSide: true,
            columns: [
                { data: 'id' },
                { data: 'name' },
                { data: 'price' }
            ]
        })
    </script>
</body>
</html>
```

- `processing` show a loading indicator when making the request.
- `serverSide` makes the request to the server (API) for all operations.  
- As mentioned earlier, Laravel can't utilize the `start` query string, so we need to write some JavaScript to generate the `page` query string here.

## Run project 

```
php artisan serve
```

Open the web browser and goto http://localhost:8000  
  
You will find this test page.

![default test page](https://blog.stackpuz.com/media/posts/3/default.png)

## Testing 

### Page size test 

Change page size by selecting 25 from the "entries per page" drop-down. You will get 25 records per page, and the last page will change from 10 to 4.

![page size test](https://blog.stackpuz.com/media/posts/3/page-size.png)

### Sorting test 

Click on the header of the first column. You will see that the id column will be sorted in descending order.

![sorting test](https://blog.stackpuz.com/media/posts/3/sort.png)

### Search test 

Enter "no" in the search text-box, and you will see the filtered result data.

![search test](https://blog.stackpuz.com/media/posts/3/search.png)

## Conclusion 

In this article, you have learned how to create a Laravel API to work with the DataTables. Understand all the DataTables parameters sent to the API and utilize them to produce the appropriate data and send it back. You also learn how to setup the DataTables on the client-side using HTML and JavaScript. I hope this article will help you, when you want to use the DataTables in your project.

Source code: [https://github.com/stackpuz/Example-DataTables-Laravel-11](https://github.com/stackpuz/Example-DataTables-Laravel-11)

Create a CRUD Web App in Minutes: [https://stackpuz.com](https://stackpuz.com)