How to pass filtered data to Datatables on Laravel?

Question

In the app I'm working on, we have two data sources: MySQL and Elasticsearch. The ES has, in rough terms, a copy of MySQL data for better performance. I built a management panel that accesses the MySQL directly. And this was OK for writing operations and retrieving a single item. However, when I try to list and filter lots of records it's very slow, causing time-outs. Then, I'm updating just the listing part to get the data from ES instead of MySQL.

The panel was built on Laravel 5.3 and we're using the Datatables plug-in for jQuery to display the data. To bind Datatables to the back-end, we have the Datatables package for Laravel.

With the following controller action, and when not applying any filter, it works fine:

public function data(Elasticsearch $elastic, Request $request)
{
    $query = [];
    $cols = [];
    if ($request->has('columns')) {
        foreach ($request->get('columns') as $column) {
            $cols[] = $column['name'];
            if (!empty($column['search']['value'])) {
                $query[] = sprintf('%s:%s', $column['name'], $column['search']['value']);
            }
        }
    }

    $from = (int) $request->get('start');
    $size = (int) $request->get('length');
    $sort = [];
    if ($request->has('order')) {
        foreach ($request->get('order') as $order) {
            $sort[] = [ $cols[$order['column']] => $order['dir'] ];
        }
    } else {
        $sort[] = [ 'name' => 'asc' ];
    }
    $args = [
        'index' => 'acme',
        'type' => 'user',
        'from' => $from ?: 0,
        'size' => $size ?: 10,
        'sort' => $sort,
    ];
    if (count($query) > 0) {
        $args['q'] = implode(' AND ', $query);
    }

    $response = $elastic->search($args);
    $data = [];
    $total = 0;

    if (!empty($response['hits'])) {
        $total = $response['hits']['total'];
        $data = array_map(function ($hit) {
            return $hit['_source'];
        }, $response['hits']['hits']);
    }

    return Datatables::of(collect($data))
        ->skipPaging()
        ->setTotalRecords($total)
        ->make(true);
}

The Javascript that triggers the table rendering looks like this:

$('#users-table').DataTable({
    processing: true,
    serverSide: true,
    orderCellsTop: true,
    ajax: '{!! route('user.data') !!}',
    columns: [
        { data: 'name', name: 'name' },
        { data: 'type', name: 'type' },
        { data: 'status', name: 'status' }
    ]
});

The issues appear when I try to search or filter the table by some column. The pagination stops to work. I guess that's because I'm filtering the dataset before passing it to Datatables, in Datatables::of(). I know I can retrieve the entire dataset from ES and let Datatables do the filtering and sorting data, but I'll probably end up with time and memory usage issues. We have millions of documents indexed.

I tried to override the global search using an empty function (->filter(function () {})), but it didn't work. The pagination breaks when I add this, showing only a single page even if the total of records is set to a big value.

Answer 1

As I commented on Gyrocode's answer, I've found a way to make it work the way I wanted. I guess there are more elegant ways to do it, but this one worked.

Since the Datatables library I'm using takes the current request to sort and filter the data, I've looked into the source code for a way to use an empty request. I ended up finding that is possible to instantiate the engine passing the request I want.

Here's what I did:

use Yajra\Datatables\Engines\CollectionEngine;
use Yajra\Datatables\Request as DatatablesRequest;

// ...

return value(new CollectionEngine(collect($data), new DatatablesRequest()))
    ->setTotalRecords($total)
    ->make‌​(true);

This way, the lib uses the data as it is and don't do any kind of filtering or sorting.