Laravel datatables not working with serverside when using get or post

Question

I am using a simple datatables but as soon as I use get and laravel request to get the get information, Datatables cannot work anymore on serverside: true.

JS

serverSide: true,
processing: true,
ajax: {
   url: "{!! route('listOfProjectsAjax') !!}",
   type: "GET",
   data: ajaxData,
   dataType: "JSON"
   },
columns: [...

Controller

public function ListOfprojects(Request $request)
  {
    $inputs = $request->all();
    return $this->projectRepository->getListOfProjects($inputs);
  }

Repository

  public function getListOfProjects($where = null)
  {


    $projectList = $this->project
      ->select( 'id','customer_name','project_name','otl_project_code','project_type','activity_type','project_status','meta_activity','region',
            'country','domain','description','estimated_start_date','estimated_end_date','comments','LoE_onshore','LoE_nearshore',
            'LoE_offshore','LoE_contractor','gold_order_number','product_code','revenue','win_ratio');
    $data = Datatables::of($projectList)->make(true);
    return $data;
  }

I don't understand why.

I did again some testing and the serverside with sorting, search is not working when I have the command:

data: ajaxData,

from my ajax request.

If I remove it, then everything works fine and I can do a serverside search, sort, ...

Here are the 2 responses from the server: Without data: ajaxData, :

{"draw":3,"recordsTotal":7,"recordsFiltered":7,"data":[{"id":66,"customer_name":"Cisco","project_name":"LAN2","project_type":"","activity_type":"","meta_activity":"","region":"","country":"","customer_location":"","domain":"","description":null,"estimated_start_date":"0000-00-00","estimated_end_date":"0000-00-00","comments":null,"LoE_onshore":0,"LoE_nearshore":0,"LoE_offshore":0,"LoE_contractor":0,"gold_order_number":"","product_code":"","revenue":10,"project_status":"","otl_project_code":"","win_ratio":0,"created_by_user_id":1,"otl_validated":0,"created_at":"2017-06-16 18:14:53","updated_at":"2017-06-16 20:23:26"}, ...],"queries":[{"query":"select count(*) as aggregate from (select '1' as `row_count` from `projects`) count_row_table","bindings":[],"time":0.18},{"query":"select * from `projects` order by `customer_name` asc limit 10 offset 0","bindings":[],"time":0.27}],"input":{"draw":"3","columns":[{"data":"id","name":"id","searchable":"false","orderable":"true","search":{"value":"","regex":"false"}},{"data":"project_name","name":"project_name","searchable":"true","orderable":"true","search":{"value":"","regex":"false"}},{"data":"customer_name","name":"customer_name","searchable":"true","orderable":"true","search":{"value":"","regex":"false"}},{"data":"otl_project_code","name":"otl_project_code","searchable":"false","orderable":"true","search":{"value":"","regex":"false"}},{"data":"project_type","name":"project_type","searchable":"false","orderable":"true","search":{"value":"","regex":"false"}},{"data":"meta_activity","name":"meta_activity","searchable":"false","orderable":"true","search":{"value":"","regex":"false"}},{"data":"region","name":"region","searchable":"true","orderable":"true","search":{"value":"","regex":"false"}},{"data":"country","name":"country","searchable":"true","orderable":"true","search":{"value":"","regex":"false"}},{"data":"domain","name":"domain","searchable":"true","orderable":"true","search":{"value":"","regex":"false"}},{"data":"description","name":"description","searchable":"false","orderable":"true","search":{"value":"","regex":"false"}},{"data":"estimated_start_date","name":"estimated_start_date","searchable":"true","orderable":"true","search":{"value":"","regex":"false"}},{"data":"estimated_end_date","name":"estimated_end_date","searchable":"true","orderable":"true","search":{"value":"","regex":"false"}},{"data":"comments","name":"comments","searchable":"false","orderable":"true","search":{"value":"","regex":"false"}},{"data":"LoE_onshore","name":"LoE_onshore","searchable":"false","orderable":"true","search":{"value":"","regex":"false"}},{"data":"LoE_nearshore","name":"LoE_nearshore","searchable":"false","orderable":"true","search":{"value":"","regex":"false"}},{"data":"LoE_offshore","name":"LoE_offshore","searchable":"false","orderable":"true","search":{"value":"","regex":"false"}},{"data":"LoE_contractor","name":"LoE_contractor","searchable":"false","orderable":"true","search":{"value":"","regex":"false"}},{"data":"gold_order_number","name":"gold_order_number","searchable":"false","orderable":"true","search":{"value":"","regex":"false"}},{"data":"product_code","name":"product_code","searchable":"false","orderable":"true","search":{"value":"","regex":"false"}},{"data":"revenue","name":"revenue","searchable":"true","orderable":"true","search":{"value":"","regex":"false"}},{"data":"project_status","name":"project_status","searchable":"false","orderable":"true","search":{"value":"","regex":"false"}},{"data":"win_ratio","name":"win_ratio","searchable":"false","orderable":"true","search":{"value":"","regex":"false"}},{"data":"","name":"actions","searchable":"false","orderable":"false","search":{"value":"","regex":"false"}}],"order":[{"column":"2","dir":"asc"}],"start":"0","length":"10","search":{"value":"","regex":"false"}}}

With data: ajaxData, :

{"draw":0,"recordsTotal":6,"recordsFiltered":6,"data":[{"id":65,"customer_name":"cxc","project_name":"LAN1","project_type":"","activity_type":"","meta_activity":"","region":"","country":"","customer_location":"","domain":"","description":null,"estimated_start_date":"0000-00-00","estimated_end_date":"0000-00-00","comments":null,"LoE_onshore":0,"LoE_nearshore":0,"LoE_offshore":0,"LoE_contractor":0,"gold_order_number":"","product_code":"","revenue":0,"project_status":"","otl_project_code":"","win_ratio":0,"created_by_user_id":1,"otl_validated":0,"created_at":"2017-06-16 18:14:37","updated_at":"2017-06-16 18:14:37"},...}],"queries":[{"query":"select count(*) as aggregate from (select '1' as `row_count` from `projects` where not exists (select * from `activities` where `activities`.`project_id` = `projects`.`id`)) count_row_table","bindings":[],"time":0.83},{"query":"select * from `projects` where not exists (select * from `activities` where `activities`.`project_id` = `projects`.`id`)","bindings":[],"time":0.76}],"input":{"unassigned":"true"}}

Here is my ajaxData:

function ajaxData(){
          var obj = {
            'unassigned': 'true'
          };
          return obj;
        }

Unknown Unknown · Accepted Answer · 2017-06-16T23:49:30

Your problem most likely resides with your select('*') in conjunction with ->make() usage. The package maintainer states to NEVER use that as it may result in unexpected behavior. Read the warning at the top of this example:

https://datatables.yajrabox.com/fluent/basic

Try changing the controller to this:

$projects = DB::table('projects')
            ->select(['id', 'field1', 'field2', 'created_at', 'updated_at']);

return Datatables::of($projects)->make();

The examples also show usage with a 'GET' request rather than a 'POST'. Do you require using 'POST' for some reason? You can also refine the quest server side using the request input like so:

$projects = DB::table('projects')
            ->select(['id', 'field1', 'field2', 'created_at', 'updated_at'])
            ->where('unassigned', $request->input('unassigned'));

Laravel datatables not working with serverside when using get or post

2 Answers