Datatables empty with a json response and AJAX OK

Arden54

Hi everyone, I'm using the plug-in datatables to print (in the first step) some data from my DB (postgresql) in my website.
To realise this, I do a custom request ( I am on symfony 3 / doctrine 2 / php 7) (this request is in a function in a special repository, every road and call are correct ( I think..) :

$em=$this->getEntityManager();

    $query = $em->createQuery(
        'SELECT p.cip, p.nom, p.extractionDexter
         FROM PharmagestsiteBundle:Pharmacie p
         '
    );

    $products = $query->getArrayResult();

    return $products;

I need to print 4 rows of 3 columns ( cip, nom, extractionDexter).
This is a part of my html to display the datatables :

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/jq-2.2.4/jszip-3.1.3/pdfmake-0.1.27/dt-1.10.15/af-2.2.0/b-1.3.1/b-colvis-1.3.1/b-flash-1.3.1/b-html5-1.3.1/b-print-1.3.1/cr-1.3.3/fc-3.2.2/fh-3.1.2/kt-2.2.1/r-2.1.1/rg-1.0.0/rr-1.2.0/sc-1.4.2/se-1.2.2/datatables.min.css"/>

<script type="text/javascript" src="https://cdn.datatables.net/v/dt/jq-2.2.4/jszip-3.1.3/pdfmake-0.1.27/dt-1.10.15/af-2.2.0/b-1.3.1/b-colvis-1.3.1/b-flash-1.3.1/b-html5-1.3.1/b-print-1.3.1/cr-1.3.3/fc-3.2.2/fh-3.1.2/kt-2.2.1/r-2.1.1/rg-1.0.0/rr-1.2.0/sc-1.4.2/se-1.2.2/datatables.min.js"></script>

cip	nom	extraction dexter

<script type="text/javascript" charset="utf-8">
    $(document).ready(function() {
        $('#example').DataTable({
        //   "bProcessing": true,
            "bServerSide": true,

            "sAjaxSource": "{{ path("pharmacie_ajax") }}",
        });
    });
</script>

My php page to get data from DB :

  $data = $this->getDoctrine()->getRepository('PharmagestsiteBundle:Pharmacie')->affichagepharma();

$res=json_encode($data);

    return new Response($res, 200, array('Content-Type' => 'application/json'));

Finally, the response from ajax in my internet website :

0:
cip: 1000001
nom: "Des vallées"
extractionDexter: true
1:
cip: 1000002
nom: "De la Vesubie"
extractionDexter: true
2:
cip: 1000003
nom: "de l Escarene"
extractionDexter: true
3:
cip: 1000004
nom: "deux mille"
extractionDexter: false

So I can understand that my data test are correctly returned but I think I'm missing something because my datatables is empty. There is only the column names with the "search" and the "show entries". I already looked on internet and I may be need to, after receive $data and before json_encode do something. But I don't know what.. If someone has an advice..
Thanks.

Arden54

By the way, I tried some lines like :

$emparray = array();
foreach($data as $row)
{
$emparray[]= $row;
}
$res= json_encode($emparray);

or

->fetchAll();

or

while($row=$data->fetch(PDO::FETCH_ASSOC)

To get an array, but nothing changes the datatables is still empty.

Arden54

UP please...

bindrid

by default DataTables is expecting something back that looks like this when server side is true by default

{ draw:1,
  recordsFilter: 56,
recordsTotal: 56,
data:[your data here]}

as described here:
https://datatables.net/manual/server-side

are you providing this?

Arden54

I can't see those lines in the response on my web site.. The ajax response looks like the picture in the attachment.

Also, I have this as response of the datatables :
sEcho: "1"
iColumns: "3"
sColumns: ",,"
iDisplayStart: "0"
iDisplayLength: "10"
...

I think I have to convert the result of the DQL query to an array and then json_encode the array but I'm not sure and I already looked on differents sites but no issues

allan

sEcho, iDisplayStart etc are legacy parameters. Also I think that you mean that those parameters are in the request from DataTables - not the response.

If you want to use server-side processing you need to process those parameters and also return the parameters that DataTables needs. The full documentation for that is available in the manual as @bindrid rightly notes.

Note that because you are using the legacy sAjaxSource option DataTables is automatically dropping into legacy mode. The legacy documentation is here, but if you can, I'd suggest updating to the newer version as the next major update will not have built in support for the old style parameters.

Allan

sureshkonduru

Datatable.destroy(); helps you to clear the datatable binded data.

Arden54

Hi,
I'm a bit disappointed now since I did like in the example.
I don't understand what I have to modify in the jquery ? Should I add new parameters( other than bServerSide and sAjaxSource) ?
I don't think that i need to use datatable.destroy since my datatables is empty atm.
Also, I'm reading the documentation but no issues

Edit : I modified the parameters to fix the newer version, I have now :

  serverSide: true,
  processing: true,
  ajax: "{{ path("pharmacie_ajax") }}",

The ajax response is still the same.

bindrid

How many rows are you going to be returning? If you are only going to pass back three, severSide should be set to false. That way, your server returns all of the rows, and DataTables does all the heavy lifting.

<script type="text/javascript" charset="utf-8">
    $(document).ready(function() {
        $('#example').DataTable({
            "serverSide": false,
            "sAjaxSource": {
                    url:"{{ path("pharmacie_ajax") }}",
                   srcData:"data"
             } 
        });
    });
</script>

Note I added the srcData above, that is the default and matches the configuration shown below. If your data is just a straight array and not inside an object called data, change srcData above to an empty string. If that still does not fix the problem, you also have the option of running ajax before you initialize the table and use that's success to initialize the datatable

{data:[
{
cip: 1000001,
nom: "Des vallées",
extractionDexter: true},
{
cip: 1000002,
nom: "De la Vesubie",
extractionDexter: true},
{
cip: 1000003,
nom: "de l Escarene",
extractionDexter: true},
{
cip: 1000004,
nom: "deux mille",
extractionDexter: false}
]

Arden54

Hello,
no, those lines are just data test to try to display the datatables. After this, I will return a lot of rows (thats why I choose server-side).

I tried each possibility you said but each time I have this error :

No route found for "GET /[object%20Object]" (from "http://localhost/projet/web/app_dev.php/pharmacie")

In fact to create the datatables I use 3 differents functions : for the dql :

public function affichagepharma(){

    $em=$this->getEntityManager();

    $query = $em->createQuery(
        'SELECT p.cip, p.nom, p.extractionDexter
         FROM PharmagestsiteBundle:Pharmacie p
         '
    );

    $products = $query->getArrayResult();
    return $products;
}

for the database :

/**
* @Route("/pharmacie/ajax", name="pharmacie_ajax")
*/
public function ajaxlistepharma()
{

    $data = $this->getDoctrine()->getRepository('PharmagestsiteBundle:Pharmacie')->affichagepharma();
       $emparray = array();

       foreach($data as $row)
        {
            $emparray[] = $row;
        }

        $res= json_encode($emparray);


    return new Response($res, 200, array('Content-Type' => 'application/json'));
}

and the basic for the URL :

/**
* @Route("/pharmacie", name="pharmacie")
*/
public function listepharmaAction()
{
return $this->render('PharmagestsiteBundle:Site:pharmacie.html.twig');
}

The data looks like still an object.. is that good ?

Arden54

Problem solved, I added this and now it works fine :

$emparray = array();
$emparray['data']= array();

       foreach($data as $row)
        {
            $emparray['data'][] = [$row['cip'], $row['nom'], $row['extractionDexter']];
        }

        $res= json_encode($emparray);

Nevertheless, I have the datatables but the search toolbar and filters doesn't work, is that normal ? I mean I press the button to filter on "cip" like example, I got the "processing" message for a sec but no changes on the datatables. Same for toolbar, I try a search on a name but the datatables doesn't modifiy. However, I can see the ajax request in the console if I try to use those buttons.

Arden54

DAMN! I just put serverside: false and.. ALL WORK FINE!!

But... Is that correct? I mean after i will get a lot of rows so what is the difference between processing and serverside true false???

Thanks you @allan and @bindrid by the way!!

bindrid

All the processing:true does is to display the "Processing...." busy indicator during the round trip to the server.

serverSide: false is actually default and this is as it should be unless you are return lots of data. I think I read somewhere that @allen rule of thumb is 50,000 rows and below is serverSide set to false. My rule of thumb is 10,000 rows but my pages run inside a slow corporate network.

allan

Yeah - its a very grey area the number to use. Typically when you get into tens of thousands its time to start thinking about using server-side processing.

DAMN! I just put serverside: false and.. ALL WORK FINE!!

Sounds like, and looks like from the code above, that your server-side script wasn't implementing server-side processing.

Allan