KTDatatable with Laravel

Okay, so I recently started a project that is using the Metronic Admin Theme by Keenthemes. One of the pages required a table, some pretty standard stuff. I opted to use the KTDatatable with Laravel, I chose it as it appeared to look close to what I wanted.

After creating the page, I started to write the function in Laravel which returns the data to populate the table, along with some pagination. This should be a simple task which requires no more than extending the model with paginate and returning the data in JSON.

Everything seemed to look fine, so far so good!

After some quick “testing” I realised that the pagination didn’t actually work. What?

I looked at the requests being made and found that the parameters being passed by KTDatatable were not ones traditionally used with Laravel pagination, no problem! I’ll just make a few modifications to the function to take these into account and it should work, right? Guess again!

KTDatatable was expecting data in the following format:

{
    "meta": {
        "page": 1,
        "pages": 1,
        "perpage": 10,
        "total": 1,
        "sort": "asc",
        "field": "name"
    },
    "data": [
        {
            "id": 1,
            "name": "Test",
            "file": "test.csv",
            "status": 0
        },
    ],
}

While Laravel pagination returns the following:

{
  "current_page": 1,
  "data": [
    {
      "id": 1,
      "name": "Test",
      "file": "test.csv",
      "status": 0,
      "created_at": "2020-01-09 23:16:56",
      "updated_at": "2020-01-09 23:16:56",
      "completed_at": null
    }
  ],
  "first_page_url": "/imports?page=1",
  "from": 1,
  "last_page": 1,
  "last_page_url": "/imports?page=1",
  "next_page_url": "/imports?page=1",
  "path": "/imports",
  "per_page": 1,
  "prev_page_url": null,
  "to": 1,
  "total": 1
}

Okay, so the formats are a little different. In my mind, there are two simple ways to resolve this. I could create a Laravel Resource Collection which allows me to specify the data format, or I could rewrite my code so it matches the format needed by KTDatatable.

Below I will show you both methods and you can decide which one is best for you.

Method #1: Resource Collection

In this method, I will show you how to create a Resource Collection and how to customise the output.

The first thing you will need to do is create a Resource Collection for your Model. To do this you must run the following command:

php artisan make:resource ImportCollection

A new file should be created in your app/Http/Resources folder called ImportCollection.php. Once the resource collection has been created, you can start defining any metadata that should be included in the response:

return [
    'data' => $this->collection,
    'meta' => $request->meta,
];

In my Controller, I extended the Request class with the metadata I needed.

// Set the meta info
$request->meta = [
    "page" => $page,
    "pages" => ceil($total / $per_page),
    "perpage" => $per_page,
    "total" => $total,
    "sort" => $order_sort,
    "field" => $order_field
];

I then return the Resource Collection as JSON data.

// Return the items in JSON format
return response()->json(new ImportCollection($results));

Method #2:

In this method, I will show you how to customise the output without creating a Resource Collection. It’s a lot like the last method, except we create an array with the metadata and the items.

// Create an array with the items and meta data
$imports = [
    'meta' => [
        "page" => $page,
        "pages" => ceil($total / $per_page),
        "perpage" => $per_page,
        "total" => $total,
        "sort" => $order_sort,
        "field" => $order_field
    ],
    'data' => $results->toArray()
];

Then I return the array as JSON data.

// Return the array in JSON format
return response()->json($imports);

Additional Info

Both methods required me to write some additional code to use the request parameters and create the metadata. Below is the code I’ve written to do this.

// Define the page and number of items per page
$page = 1;
$per_page = 10;

// Define the default order
$order_field = 'name';
$order_sort = 'asc';

// Get the request parameters
$params = $request->all();
$query = Import::query();

// Set the current page
if(isset($params['pagination']['page'])) {
    $page = $params['pagination']['page'];
}

// Set the number of items
if(isset($params['pagination']['perpage'])) {
    $per_page = $params['pagination']['perpage'];
}

// Set the search filter
if(isset($params['query']['generalSearch'])) {
    $query->where('name', 'LIKE', "%" . $params['query']['generalSearch'] . "%")
    ->orWhere('file', 'LIKE', "%" . $params['query']['generalSearch'] . "%");
}

// Set the status filter
if(isset($params['query']['Status'])) {
    $query->where('status', $params['query']['Status']);
}

// Set the sort order and field
if(isset($params['sort']['field'])) {
    $order_field = $params['sort']['field'];
    $order_sort = $params['sort']['sort'];
}

// Get how many items there should be
$total = $query->limit($per_page)->count();

// Get the items defined by the parameters
$results = $query->skip(($page - 1) * $per_page)
->take($per_page)->orderBy($order_field, $order_sort)
->get();

And that’s it! Using either method you should be able to use KTDatatable with Laravel successfully.