README

This library provides a helper to process filters and sorting for the ColumnControl plugin for datatables.net.

Usage

Inside the method in your controller:

if($request->ajax())
{
    $users = \App\Models\User::select('*');

    $data_source = new DatatablesEloquent();
    $data_source->apply($users, $request);

    return $data_source->getResponse();
}
else
{
    return view('test');
}

You can pass the frontend timezone to the constructor (the default is 'Euurope/Rome'), alternatively you can set it later with the setTimezone method

$my_data_source = new DatatablesEloquent(); // This has as its timezone "Europe/Rome"

// These two options are equivalent
$data_source = new DatatablesEloquent("America/Chicago");
$data_source->setTimezone("America/Chicago");

Logically, all necessary libraries should be included in the frontend.

<!doctype html>
<html lang="it">
    <head>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <title>Test</title>

        <link href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.3.0/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-9ndCyUaIbzAi2FUVXJi0CjmCapSmO7SnpJef0486qhLnuZ2cdeRhO02iuK6FUUVM" crossorigin="anonymous">
        <link href="https://cdn.datatables.net/v/bs5/jq-3.7.0/moment-2.29.4/jszip-3.10.1/dt-2.3.7/b-3.2.6/b-colvis-3.2.6/b-html5-3.2.6/cr-2.1.2/cc-1.2.0/date-1.6.3/fc-5.0.5/r-3.0.8/sp-2.3.5/sl-3.1.3/sr-1.4.3/datatables.min.css" rel="stylesheet" integrity="sha384-yxsOQmelZ9gQsmj1aK2QTE0UDBuP5QObMeirRHNiJ5EcKZGjuMNkTE6hwuzc7sAs" crossorigin="anonymous">
    </head>
    <body>
        <main>
            <table id="example" class="table table-striped">
            </table>
        </main>

        <script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.3.0/js/bootstrap.bundle.min.js" integrity="sha384-geWF76RCwLtnZ8qwWowPQNguL3RmwHVBC9FhGdlKrxdiJJigb/j/68SIy3Te4Bkz" crossorigin="anonymous"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/pdfmake.min.js" integrity="sha384-VFQrHzqBh5qiJIU0uGU5CIW3+OWpdGGJM9LBnGbuIH2mkICcFZ7lPd/AAtI7SNf7" crossorigin="anonymous"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/vfs_fonts.js" integrity="sha384-/RlQG9uf0M2vcTw3CX7fbqgbj/h8wKxw7C3zu9/GxcBPRKOEcESxaxufwRXqzq6n" crossorigin="anonymous"></script>
        <script src="https://cdn.datatables.net/v/bs5/jq-3.7.0/moment-2.29.4/jszip-3.10.1/dt-2.3.7/b-3.2.6/b-colvis-3.2.6/b-html5-3.2.6/cr-2.1.2/cc-1.2.0/date-1.6.3/fc-5.0.5/r-3.0.8/sp-2.3.5/sl-3.1.3/sr-1.4.3/datatables.min.js" integrity="sha384-2YD7kNlBwNhl7PwgHU8JcvFOtRrpan0SONQld4bCirM1Dxx76pXwzpehSPTNKMVZ" crossorigin="anonymous"></script>
        <script type="text/javascript">
            new DataTable('#example', {
                columnControl: [
                    {
                        target: 0,
                        content: [
                            'order',
                            [
                                'orderAsc',
                                'orderDesc',
                                'spacer',
                                'orderAddAsc',
                                'orderAddDesc',
                                'spacer',
                                'orderRemove'
                            ]
                        ]
                    },
                    {
                        target: 1,
                        content: ['search']
                    }
                ],
                ordering: {
                    handler: false,
                    indicators: false
                },

                serverSide: true,
                ajax: '{{ \Illuminate\Support\Facades\URL::current() }}',
                colReorder: true,
                fixedColumns: true,
                columns: [
                    {
                        data: 'id', // Field name for searching/filtering
                        name: 'id', // field name for sorting
                        title: '#' // Display name
                    },
                    {
                        data: 'name', // Field name for searching/filtering
                        name: 'name', // field name for sorting
                        title: 'Nome' // Display name
                    },
                    {
                        data: 'email', // Field name for searching/filtering
                        name: 'email', // field name for sorting
                        title: 'Email' // Display name
                    },
                    {
                        data: 'created_at', // Field name for searching/filtering
                        name: 'created_at', // field name for sorting
                        title: 'Data di creazione', // Display name
                        type: 'date' // Force the field type otherwise it is recognized as a string
                    }
                ]
            });
        </script>
    </body>
</html>

The apply method accepts a third optional parameter, namely "field_map", this parameter is used to map the database fields in case fields are renamed in the generation of the response or if more than one database column corresponds to a datagrid column:

public function index(Request $request)
{
	if($request->ajax())
	{
		$users = \App\Model\User::select('users.*');
		
		$data_source = new DatatablesEloquent();
		$field_map = [ // I create the array that contains the mapping field name => column name
			'fiscal_reference' => ['users.tax_code', 'users.vat'],
			'creation_date' => 'created_at'
		];
		$data_source->apply($users, $request, $field_map);
		
		return $data_source->getResponse(function($data) {
			// Optionally you can pass a Clojure function
			// to the "getResponse" function to format the
			// data or perform operations. If it is not
			// provided the toArray() method will be used.
			return [
				'username' => $data->username;
				'fiscal_reference' => ($data->is_company())? $data->vat : $data->tax_code;
				'creation_date' => $data->created_at->format('d/m/Y');
				// ... and so on
			];
		});
	}
	else
	{
		return view('user.index')
	}
}

The "apply" method also accepts an optional fourth parameter; this is used to override the sorting behavior:

public function index(Request $request)
{
	if($request->ajax())
	{
		$contracts = \App\Model\Contracts::select('contracts.*')
										 ->join('customers', 'customers.id', '=', 'contracts.customer_id');
		
		$data_source = new DatatablesEloquent();
		$field_map = [ // I create the array that contains the mapping field name => column name
			'customer' => 'customers.id'
		];
		$field_sorting = [
			'customer' => 'customers.company_name'
		];
		$data_source->apply($contracts, $request, $field_map, $field_sorting);
		
		return $data_source->getResponse(function($data) {
			// ....
		});
	}
	else
	{
		return view('customer.index')
	}
}

In this case if we had passed only the "field_map" array to apply the sorting would have been done based on the customer id, instead by overriding it we can force the sorting by company name while still maintaining the search by id.

fazzinipierluigi / datatables-eloquent

Maintainers

Details

README

Usage