Serverside ordering Columns

[Corvisier]

Using ServerSide, only the first column of the DataTable can't be ordered. The remaining columns order just fine. If "bServerSide": false, all columns order as expected.

I took a look on debug but can't solve this issue. Regards

[Chumper]

i will look into it

[Chumper]

Confirmed using the QueryEngine. Fix is on the way.

[xDragonZ]

After updating the datatable, the issue occurred, I'm using QueryEngine too. Looking forward for the fix.

[Chumper]

Should be fixed with 2.1.0

[Corvisier]

Thanks for the fix Chumper. I really appreciate it. But I found another issue maybe related to this. The issue is using serverside if you use addColumn and render that on the first column on the view, the remaining columns order just skips a column.

Like in the picture above if I try to order the # Column it just orders by the nif and so on.

Best regards

[Chumper]

So you can still not sort by the first column? Are you using collections or the query engine?

[Corvisier]

Yes I can sort by first column. I'm using queryEngine.The fix you provided works great. But in this particular case where a column is added to the view it does sort but the headers arent right. If I click on # it sorts by the NIF . But only in this use case when there's a added column.

When on the controller I use the addColumn method and its added to the end of the array on make(). But on the view I'm rendering the added column on the first column (the plus sign on the screenshot) and a set it to bSortable= false

Can you reproduce the issue too or is it just me?

Best regards

[Chumper]

I think i understand but can you show me your server side declaration and client declaration just to make sure?

[Corvisier]

The controller:

public function api_all_customers()
    {
      $prefix = DB::getTablePrefix();

      $customers = DB::table('customers')
      ->join('countries', 'customers.country_id', '=', 'countries.id')
      ->join('states', 'customers.state_id', '=', 'states.id')
      ->join('regions', 'customers.region_id', '=', 'regions.id')
      ->select(array('customers.id as cid', 'customers.taxnumber', DB::raw('concat('.$prefix.'customers.firstname," ",'.$prefix.'customers.lastname) as name'), DB::raw('concat('.$prefix.'customers.address," ",'.$prefix.'customers.address2) as address'), 'customers.postalcode', 'countries.country_name', 'states.state_name', 'regions.region_name', 'customers.city', 'customers.email', 'customers.website', 'customers.phonenumber', 'customers.mobilenumber', 'customers.faxnumber', 'customers.deleted_at'));

      $concat = DB::raw('concat('.$prefix.'customers.firstname," ",'.$prefix.'customers.lastname)');
      $concat2 = DB::raw('concat('.$prefix.'customers.address," ",'.$prefix.'customers.address2)');

      return Datatable::query($customers)
      ->showColumns('cid', 'taxnumber', 'name', 'address', 'postalcode', 'country_name', 'state_name', 'region_name', 'city', 'email', 'website', 'phonenumber', 'mobilenumber', 'faxnumber', 'deleted_at')
      ->setSearchWithAlias('cid')
      ->searchColumns('customers.id', 'customers.taxnumber', $concat, $concat2, 'customers.email', 'customers.phonenumber', 'customers.mobilenumber', 'customers.deleted_at')
      ->addColumn('details', function()
      {
        return '<span class="row-details row-details-close"></span>';
      })
      ->make();
    }

The json response

{"aaData":[[1,"123456789","Customer name","Address","4425-143","Portugal","Porto","Maia","Snatas","teste@localhost","www.localhost.com","224905969","966661234","221234567",null,"<span class=\"row-details row-details-close\"><\/span>"]],"sEcho":0,"iTotalRecords":10005,"iTotalDisplayRecords":1}

The js

var all_customers = $('.all_customers').dataTable({
    "bProcessing": true,
    "bServerSide": true,
    "bAutoWidth": false,
    "sAjaxSource": base+"/api/v1/customers/all",
    "sScrollX": "100%",
    "aoColumnDefs": [
    {
      /** Customers Details Column */
      "mRender": function (data, type, full) {
        return full[15];
      },
      "aTargets": [0]
    },

    {
      /** Customers ID Column */
      "mRender": function (data, type, full) {
        return full[0];
      },
      "aTargets": [1]
    },

    {
      /** Customers Taxnumber Column */
      "mRender": function (data, type, full) {
        return full[1];
      },
      "aTargets": [2]
    },

    {
      /** Customers Name Column */
      "mRender": function (data, type, full) {
        return '<a class="roll-link" href="/customers/' + full[0] + '"><span data-title="'+ full[2] + '">' + full[2] + '</span></a>';
      },
      "aTargets": [3]
    },

    {
      /** Customers Email Column */
      "mRender": function (data, type, full) {
        return full[9];
      },
      "aTargets": [4]
    },

    {
      /** Customers Phonenumber Column */
      "mRender": function (data, type, full) {
        return full[11];
      },
      "aTargets": [5]
    },

    {
      /** Customers Mobilenumber Column */
      "mRender": function (data, type, full) {
        return full[12];
      },
      "aTargets": [6]
    },

    {
      /** Customers Status Column */
      "mRender": function (data, type, full) {
        if (full[14] == null) {
          return '<span class="label dtable label-success center-block">Activo</span>';
        } else {
          return '<span class="label dtable label-danger center-block">Inactivo</span>';
        }
      },
      "aTargets": [7]
    },

    { 
      "sWidth": "70px", "aTargets": [7]
    },

    { 
      "sClass": "text-center", "aTargets": [0]
    },

    { 
      "sClass": "text-center", "aTargets": [1]
    },

    { 
      "bSortable": false, "aTargets": [0]
    }

    ]
  });

The view:

    <table class="table table-striped table-hover table-condensed all_customers">
      <thead>
        <tr class="header-color">
          <th></th>
          <th>#</th>
          <th>{{{ Lang::get('customers.taxnumber') }}}</th>
          <th>{{{ Lang::get('customers.firstname') }}}</th>
          <th>{{{ Lang::get('customers.email') }}}</th>
          <th>{{{ Lang::get('customers.phonenumber') }}}</th>
          <th>{{{ Lang::get('customers.mobilenumber') }}}</th>
          <th>{{{ Lang::get('customers.status') }}}</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td colspan="8" class="dataTables_empty text-center">{{{ Lang::get('common.loadingdata') }}}</td>
        </tr>
      </tbody>
    </table>

[norrin77]

Hello Corvisier and Chumper

Sorry for the little offtopic. I've seen the issue you post about Chumper datables and your sample code. I'm also trying to put laravel and datables together, but whenever I call .datatable(...) using jquery the table is automatically populated with the data provided by the laravel controller, ignoring my aoColumnDefs (which provide styling, etc.) from my custom JS.

Am I missing something?

My custom JS is set on the laravel view, by the way. It is not clear on your issue sample code but I guess you have also put it there.

Kind regards.

[Chumper]

can you show some code? @Corvisier i need to look into that a little bit closer.

[norrin77]

My code was similar to Corvisier's. That was what was driving me crazy.

Solved by just upgrading jquery datatable plugin from 1.9.2 to 1.9.4.

How awesone is software, isn't it? Oo

Thanks anyway.

[Chumper]

@Corvisier If you do it like this: does it work correctly?

 return Datatable::query($customers)
      ->addColumn('details', function()
      {
        return '<span class="row-details row-details-close"></span>';
      })
      ->showColumns('cid', 'taxnumber', 'name', 'address', 'postalcode', 'country_name', 'state_name', 'region_name', 'city', 'email', 'website', 'phonenumber', 'mobilenumber', 'faxnumber', 'deleted_at')
      ->setSearchWithAlias('cid')
      ->searchColumns('customers.id', 'customers.taxnumber', $concat, $concat2, 'customers.email', 'customers.phonenumber', 'customers.mobilenumber', 'customers.deleted_at')
      ->make();

You need to make sure the order on the serverside is the same as on the clientside. If you want a custom column first you also need to declare it first on the server side.