Jquery datatables, server side custom sorting

Question

I am using Laravel 4.2 with server side jQuery data tables.

Package: https://github.com/Chumper/Datatable

How to add custom sorting?

as for columns of currency, time, etc.

Answer 1

NOTE. This did not use the Chumper / Datatable package, but it uses jQuery datatables, so it can be useful.

Here is how I did it. It was a scenario in which I had a table with American football teams. Each team participated in the Conference, which was part of the Division. Teams can be sorted by team name, conference, or department. The following is the server code used to implement it. In addition, they can be filtered by a conference or unit.

/* * Route::get( 'api/v1/teams-table', ' APIController@teamsTable ' ); */ public function dataTable() { // get the input parameters $i = Input::all(); // parse the parameters and set default values $draw = isset( $i[ 'draw' ] ) ? $i[ 'draw' ] : 1; $start = isset( $i[ 'start' ] ) ? $i[ 'start' ] : 0; $length = isset( $i[ 'length' ] ) ? $i[ 'length' ] : 10; $search = isset( $i[ 'search' ][ 'value' ] ) && '' != $i[ 'search' ][ 'value' ] ? $i[ 'search' ][ 'value' ] : false; $ordrby = isset( $i[ 'order' ] ) ? $i[ 'columns' ][ $i[ 'order' ][ 0 ][ 'column' ] ][ 'name' ] : ''; $ordrdr = isset( $i[ 'order' ] ) ? $i[ 'order' ][ 0 ][ 'dir' ] : 'asc'; $total = Team::count(); $filter = $total; // get the data if ( '' == $search ) { switch( $ordrby ) { case 'name': $teams = Team::with( 'conferences', 'logo', 'conferences.division' ) ->skip( $start ) ->take( $length ) ->orderBy( 'name', $ordrdr ) ->get(); break; case 'conference': $teams = Team::with( 'conferences', 'logo', 'conferences.division' ) ->join( 'conference_team', 'conference_team.team_id', '=', 'teams.id' )->join( 'conferences', 'conferences.id', '=', 'conference_team.conference_id' ) ->orderBy( 'conferences.abbr', $ordrdr ) ->skip( $start ) ->take( $length ) ->get(); break; case 'division': $teams = Team::with( 'conferences', 'logo', 'conferences.division' ) ->skip( $start ) ->take( $length ) ->conference() ->division() ->orderBy( 'abbr', $ordrdr ) ->get(); break; default: $teams = Team::with([ 'conferences', 'logo', 'conferences.division' ]) ->skip( $start ) ->take( $length ) ->get(); } } else { $teams = Team::with( 'conferences', 'logo', 'conferences.division' ) ->skip( $start ) ->take( $length ) ->where( 'name', 'LIKE', '%' . $search . '%' ) ->orWhereHas( 'conferences', function( $q ) use ( $search ) { $q->where( 'abbr', 'LIKE', '%' . $search . '%' ) ->orWhereHas( 'division', function( $qu ) use ( $search ) { $qu->where( 'abbr', 'LIKE', '%' . $search . '%' ); }); }) ->get(); $filter = Team::with( 'conferences', 'logo', 'conferences.division' ) ->where( 'name', 'LIKE', '%' . $search . '%' ) ->orWhereHas( 'conferences', function( $q ) use ( $search ) { $q->where( 'abbr', 'LIKE', '%' . $search . '%' ) ->orWhereHas( 'division', function( $qu ) use ( $search ) { $qu->where( 'abbr', 'LIKE', '%' . $search . '%' ); }); }) ->count(); } // loop through the retrieved data and format it to be returned as JSON $data = []; foreach ( $teams as $t ) { $show = URL::route( 'admin.team.show', $t->slug ); $edit = URL::route( 'admin.team.depth_chart', $t->slug ); $data[] = [ 'checkbox' => '<label><input type="checkbox" class="ace" value="' . $t->id . '" /><span class="lbl"></span></label>', 'logo' => '<img src="' . $t->logo->filename . '" alt="' . $t->name . ' logo" height="40">', 'name' => [ 'display' => link_to_route( 'admin.team.show', $t->name, [ $t->slug ] ), 'filter' => $t->name, 'sort' => $t->name, ], 'conference' => [ 'display' => link_to_route( 'admin.conference.show', $t->conferences[ 0 ]->abbr, [ $t->conferences[ 0 ]->slug ] ), 'filter' => $t->conferences[ 0 ]->name . ' ' . $t->conferences[ 0 ]->abbr, 'sort' => $t->conferences[ 0 ]->abbr, ], 'division' => [ 'display' => link_to_route( 'admin.division.show', $t->conferences[ 0 ]->division->abbr, [ $t->conferences[ 0 ]->division->slug ] ), 'filter' => $t->conferences[ 0 ]->division->name . ' ' . $t->conferences[ 0 ]->division->abbr, 'sort' => $t->conferences[ 0 ]->division->abbr, ], 'site' => '<a target="_blank" href="' . $t->url . '">website <i class="fa fa-external-link"></i></a>', 'actions' => sprintf( $this->actions, $show, $edit, $show, $edit ), ]; } $tdata = [ 'draw' => $draw, 'recordsTotal' => $total, //consider caching or setting fixed value for this 'recordsFiltered' => $filter, 'data' => $data, ]; return Response::json( $tdata ); } 

In any case, you can customize this example to suit your situation. Hope this helps!

Answer 2

this is how i do it

nb it is not optimized. more or less complete code snippet without error checking

The function of the laravel controller (it is L5.2, but easily lowers it to 4.2):

  $response = array(); $query = MyModel::query(); $response['recordsTotal'] = MyModel::count(); $response['draw'] = Input::get('draw', 0); $query->where('searchField', 'LIKE', '%' . Input::get('search', array('value' => ''))['value'] . '%'); $response['recordsFiltered'] = $query->count(); $query->take(Input::get('length', 1)); $query->offset(Input::get('start', 0)); $columns = Input::get('columns'); $orders = Input::get('order', []); $data = $data->toArray(); foreach($orders as $order){ $idx = $order['column']; $column = $columns[$idx]; $orderFactor = 1; if($order['dir'] == 'desc') $orderFactor = -1; $dname = $column['data']; if(count($data)>0){ if(isset($data[0][$dname])){ usort($data, function($record1, $record2) use($dname, $orderFactor){ // here you implement your custom sorting // like if($dname === 'price') return compare_price($record1[$dname], $record2[$dname]) * $orderFactor; // but I hope you're not storing prices as strings in your database. you won't need this custom sorting // return strcmp($record1[$dname], $record2[$dname]) * $orderFactor; }); } } } $response['data'] = $data; return Response::json($response); 

ps this code assumes that the data field "data" of the data columns is called exactly the same as the name of the field in your database. You will also need to use render_function to render the data column as you wish

datatable columns:

 columns: [ { data: 'price', orderable: true, searchable: true, render: render_price }, { data: 'anotherField' }, [...] ], 

Render_function example:

  function render_price(price, type, row) { return price + ' USD'; } 

this way you will have your columns displaying the data you want (e.g. price = $ 10.50)

and they will be sortable

Answer 3

Not sure if with custom sorting you want to sort the rows by clicking on the headers? If this is what you mean, you can define them on the client side by specifying data parameters.

 oSettings = $("#{{$table->getId()}}").dataTable().fnSettings(); oSettings.aaSorting = [[6, 'desc']]; 

But if you want to support the default sorting for a specific column when loading data, then

 Datatable::table() ->addColumn($theader) // these are the column headings to be shown ->setOptions('order', array([$ordercolumn ,"desc"])) ->setUrl(route('route.name', $form_id)) ->render() 

Hope this helps.

Answer 4

Didn't you check the documentation? Because it was explained there:

 public function getDatatable() { return Datatable::collection(User::all(array('id','name'))) ->showColumns('id', 'name') ->searchColumns('name') ->orderColumns('id','name') ->make(); }