jquery datatables, custom sorting on server side

Question

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

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

How can I add custom sorting ?

like for columns of currency, time, etc

Answer 1

NOTE: this did not make use of the Chumper/Datatable package, but it does use jQuery datatables so may be of some use.

Here is how I did it. This was a scenario in which I had a table with American football teams in it. Each team was a member of a Conference, that was part of a Division. The teams could be sorted by team name, conference, or division. Below is the server-side code used to implement this. Furthermore, they could be filtered by conference or division.

/*
 * 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 );
}

With any luck, you can adjust this example to fit your situation. Hope this helps!

Answer 2

this is how I do it

n.b. it's not optimized. more or less a complete code snippet without error checking

laravel controller function (it's L5.2 but easily downgrade-able 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);

p.s. this code assumes that the 'data' field of the datatable columns is named exactly as the field name in your database. You will also need to use a render_function to render the datatable 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 datatable displaying columns the way you want (e.g. price = 10.50$)

and they will be sortable

Answer 3

If you are trying to sort the collection or query result in any order, you can do something like this:

$orders = Order::all()->SortBy('currency','desc');

or

$customers = Customers::where('age',$age)->orderBy('currency','desc')->get();

By the way, why are you still on Laravel 4 when there is Laravel 8 already?

Answer 4

Not sure if by custom sorting you mean sorting the rows by clicking on the headers? If that is what you mean then you can define them on the client side by defining the datatable settings.

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

But if you want to maintain a default sorting of a particular column when the datatable loads, 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()

I hope this helps.

Answer 5

Haven't you checked the documentation? Because it's explained there:

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