Laravel datatable sort not working how to use on join

Question

I am using Laravel Datatable, Sorting is not working, Can someone help me.

controller

Table::select(array( DB::raw('table2.con_title'),
    DB::raw('........
Datatables::of(----)->make();

View

.dataTable({ "bProcessing": true, "bServerSide": true, "sAjaxSource": ajaxurl,

"aoColumnDefs": [ { mData:'table2.con_title' , aTargets: [0]},.......

error DataTables warning (table id = '-----'): Requested unknown parameter 'table2.con_title' from the data source for row 0

Answer 1

Recently I was working with Laravel data-table and ran into the similar situation, data was loading in the columns but sorting was not working and my data-table was loading data from multiple database (DB) tables. Following are my findings:

• Make Sure your DB tables relationships are setup as per documentation https://laravel.com/docs/5.7/eloquent-relationships

• In case, you are using DB::raw($your_sql) - make sure you are referring to right DB column name in the data-table column configuration. For Example:

      $sql_query = "
      SELECT
         id AS primary_key,
         first_name,
         last_name
      FROM
         Contacts           
        ";
  
       $data = collect(DB::select(DB::raw($sql_query)));
       $list = Datatables::of($data);
       $list->make(true); 
  

• In your blade file, do the data-table column configuration like this

          <table id="name-list">
          <thead>
              <tr>
                  <th>ID</th>
                  <th>First Name</th>
                  <th>Last Name</th>
              </tr>
          </thead>
          <tbody>
  
          </tbody>
          </table>
  
       $('#name-list').dataTable({
           "processing": true,
           "serverSide": true,
           "ajax": "{{route('path_to_your_server_code')}}",
           "columns": [
                 {data: 'primary_key', name: 'primary_key', orderable: true, searchable: true, visible: true},
                 {data: 'first_name', name: 'first_name', orderable: true, searchable: true, visible: true},
                 {data: 'last_name', name: 'last_name', orderable: true, searchable: true, visible: true}],
           "order":[[1, 'desc']]
      });
  

• If you are using Eloquent-Relationships in your SQL and eager loading multiple relations (i.e. multiple DB tables), make sure you are referring to DB columns through Eloquent-Relationships e.g. relation.column_name. Your data-table column configuration will look like this:

       //column configuration
       {data: 'some_relation.db_column', name: 'some_relation.db_column', orderable: true, searchable: true, visible: true} 
  
       //complete example code
       $('#name-list').dataTable({
       "processing": true,
       "serverSide": true,
       "ajax": "{{route('path_to_your_server_code')}}",
       "columns": [
             {data: 'primary_key', name: 'primary_key', orderable: true, searchable: true, visible: true},
             {data: 'first_name', name: 'first_name', orderable: true, searchable: true, visible: true},
             {data: 'last_name', name: 'last_name', orderable: true, searchable: true, visible: true},
             {data: 'some_relation.db_column', name: 'some_relation.db_column', orderable: true, searchable: true, visible: true}],
       "order":[[1, 'desc']]
  });
  

I have tried above both ways and sorting worked for me in both the cases i.e. with DB::raw($your_sql) and Eloquent-Relationships.

Answer 2

You need to make sure that your table columns are mapped correctly to your data.

https://datatables.net/manual/tech-notes/4

Answer 3

There is an option :

ordering: true,

or

"ordering": true,