Server Side processing for ajax request

DawidGrzejek

Hello,

I have a action, where user can click button and then ajax is calling php file and on succes DataTable is created

 $.ajax({
            type: "POST",
            url: 'get_data.php',
            data: { dataForAjax },
            success: function(result){
             $('#table').DataTable({ 
                destroy: true,
                "searching": true,
                lengthChange: false,
                "aaData": result, 
                "paging": true,
                orderCellsTop: true,
                fixedHeader: true,
                "scrollY": "400px",
                "scrollCollapse": true,
                select: { style: 'multi', selector: 'td:first-child' },
                columnDefs: [
                  { targets: [0], "orderDataType": 'select-checkbox' },
                  { targets: [2], "orderable": false },
                  { targets: [4,5,6,7], visible: false },
                ],
                columns: [
                  { data: null, defaultContent: '', orderable: true, className: 'select-checkbox' },
                  { "data": "id_priv", title: "id" },
                  { "data": "env", title: "env"  },
                  { "data": "name", title: "name" },
                  { "data": "group_dn", title: "dn},
                  { "data": "id_usr", title: "id" },
                  { "data": "login", title: "login" },
                  { "data": "user_dn", title: "dn" },
                ],
                "language": {
                  "paginate": {"previous": "<","next": ">"}
                },
                initComplete: function() {
                  this.api().columns([2]).every(function() {
                    var column = this;
                    var select = $('<select><option value=""></option></select>')
                      .appendTo($(column.header()).empty())
                      .on('change', function() {
                        var val = $.fn.dataTable.util.escapeRegex(
                          $(this).val()
                        );
                        column
                          .search(val ? '^' + val + '$' : '', true, false)
                          .draw();
                      });

                    column.data().unique().sort().each(function(d, j) {
                      select.append('<option value="' + d + '">' + d + '</option>');
                    });
                  });
                },
              }); 
            }
});

get_data.php works like this. It connect to postgresql database via PDO and return array of object.

    $pdo = new PDO($dsn, $user, $pswd, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
    // send query to db
    $stmt = $pdo->query($query);
    // fetch response data
    $data = $stmt->fetchALL();
    try{
        if ($pdo) {
            return $data;
        }
    } catch (PDOException $e) {
        die($e->getMessage());
    } finally {
        if ($pdo) {
            $stmt = null;
            $pdo = null;
        }
    }
...
    $group = (object) [
        'id_priv' =>  $data[$i]['id'],
        'name' =>  $data[$i]['name'],
        'env' =>  $data[$i]['env'],
        'id_usr' => $data[$i]['id_usr'],
        'login' => $data[$i]['login'],
        'user_dn' => $data[$i]['user_dn'],
        'group_dn' => $data[$i]['group_dn']
    ];
    $available_privs [] = $group;
    
    ob_end_clean();
    header('Content-Type: application/json');
    echo json_encode($available_privs);

Everything works just fine but.... datatable is filled with ~80000 rows, which cause some trobule when many users will use my application. I wanted to change the way I'm getting result from database to server-side.

I've read docs about it and saw few examples, but cant figure how it should be done in that case.

tangerine

Why aren't you using "serverSide: true" ??
https://datatables.net/examples/server_side/simple.html

DawidGrzejek

that's what I'm trying to do. Rearange my exapmle to work on server-side. But here is that server-side script the SSP class, and I'm not sure where what is happening and how to implement in my example.

allan

Two options:

1. You could use the SSP class from the example that @tangerine linked to. It works, but its intentionally basic and is really just meant for demos (i.e. it doesn't support joins).
2. Or you can use the Editor server-side libraries, which support server-side processing and they are available under the MIT license.

Allan