ServerSide with Stored Procedure

sParentMoua

Hi,
I call a stored procedure to fill my table, but despite the Datatable being initialized as ServerSide, it gets all of the values back, which is a problem when I return 40K+ rows.

Here's an example of my initialization:

    $('#targetTable').DataTable({
            processing: true,
            serverSide: true,
            ajax: {
                url: "{{ route('dataController.getDataPages') }}",
                data: function ( d ) {
                    d.parameter1= getParameterByName('parameter1');
                    d.parameter2= getParameterByName('parameter2');
                }
            },
            columns:[
                { "data": "column1" },
                { "data": "column2" },
                { "data": "column3" },
                { "data": "column4" },
                { "data": "column5" }
            ]
        });

In my PHP (Laravel with Yajra), I call my stored procedure in this manner (after processing my parameters):

    $data= DB::select("CALL getTableData('parameter1', 'parameter2');");
     return Datatables::of($data)->make(true);

This timeout with too many rows because it loads the result of the stored procedure in my server, which is what I was trying to fix in the first place by making it "serverSide".

If there's a way to not load all the results from my stored procedure, I would like to know.

If there's no way, I would like to know if I could control the number of results (pages), control the data returned per page, control the ordering, and the paging.

Otherwise, I'll have to do it all manually (unless there's another library out there for my need?)

Thanks!

allan

Sounds like your server-side script isn't implementing server-side processing. I don't see anything in the above code that uses the parameters sent to the server, but perhaps it is hidden inside whatever your classes are?

Allan

sParentMoua

Hi Allan,
I have implemented a paging functionnality in my stored procedure. I get back the page from the URL response, and return only the data of the page that I need.

I now have other problems, such as the fact I always have the answer "No matching records found", despite getting my array correctly back from my Ajax request. Worst is that it worked before, and I'm not sure what broke it. My columns are exactly what I have back from the server, in the same order, so I'm exploring if I have the right format.

This is my server response:

  $response = array(
        "recordsTotal" => $resultCount,
        "recordsFiltered" => $resultCount,
        "data" => $returnArray
    );

And I get in the ajax callback (example):

    data:
           keynumber1 : 
                     parameter 1 : 'parameter1value'
                     parameter 2 : 'parameter2value'
           keynumber2 : 
                     parameter 1 : 'parameter1value'
                     parameter 2 : 'parameter2value'
     ...

Anyway, my initial question is answered, though. Thx for that

allan

Can you show me the exact JSON returned from the server?

Thanks,
Allan

sParentMoua

Hi Allan,

Did manage to find out what I was doing wrong.
My return array was an array of arrays, and I noticed that when I was passing an array of object (casting my row array to an object), it was in the correct format, and the table stopped complaining. What was weird is that I had to do an array slice on the return array, otherwise it kept saying the error.

Sorry to not paste my json, it was monstruously large, but all it would show you is that I was having data that looked like this:

    "arrayKey1",{"param1":"value1", "param2":"value2"},
    "arrayKey2",{"param1":"value1", "param2":"value2"} ....

And it needed to look like this:

     "[
               {"param1":"value1", "param2":"value2"},
               {"param1":"value1", "param2":"value2"}....
       ]"

Just casting each rows of my array of array to (object) worked well.

Thanks for your time investment.

allan

That would do it - great to hear you've got it working now!

Allan