jquery datatables, custom sorting on server side [closed]

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

Sinfonia

How about using something like this instead github.com/lazymofo/datagrid, or extend it with this github.com/ais-one/datagrid_ext – Groh

@AaronGong There are other options available but I have integrated datatables on several pages and I don't want rewrite. – Sinfonia

ok, good luck then, I was looking at datatables too and a few other similar crud stuff, ended up with lazymofo datagrid. – Groh

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!

Befriend

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

Baxley

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?

Velarde

"By the way, why are you still on Laravel 4 when there is Laravel 8 already?" because this is a 6 year old question? – Fourdrinier

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.

Shipment

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(); }

Sexivalent

I am asking about custom sorting, not column order or anything else. Where is custom sorting in your answer, please let me know. – Sinfonia

What do you mean with custom sorting? Please give a better explanation or example. – Sexivalent

datatables.net/plug-ins/sorting/currency - that is for client end JS solution, I am looking same on server side. – Sinfonia