How do I handle sort/search with serverside processing?

GoldenJoe

Let's say I want to grab users and their most recent activity and put it into a table with the following format:

<table id="dt-users" class="table table-striped table-bordered" cellspacing="0" width="100%">
    <thead>
    <tr>
        <th>Username</th>
        <th>Activity</th>
        <th>Date</th>
    </tr>
    </thead>
</table>

var tableSS = $('#dt-users').DataTable({
    serverSide: true,
    processing: true,
    ajax: {
        type: "POST",
        url: "http://localhost:8888/Web%20Services/MyApp/User_RequestInfo.php",
        dataSrc: "data",
    },
    columns: [
        { "data": "username"},
        { "data": "recent_activity.description"},
        { "data": "recent_activity.time"},
    ],
    pageLength: 5,
    lengthMenu: [[5, 10, 25, -1], [5, 10, 25, "All"]],
});

Username comes from a table 'users', while Activity and Date come from a table 'activities'. I already have an endpoint which takes arguments like a date range to search, activities to filter, etc. It makes a join query and returns the results I expect.

The data is formatted like this:

{
    "draw": 1,
    "recordsTotal": 3,
    "recordsFiltered": 3,
    "data": [
        {
            "id": 1,
            "username": "Joe",
            "recent_activity": {
                "id_activity": 1,
                "description": "Login",
                "time": "2017-07-27 18:55:12"
            }
        },
        {
            "id": 2,
            "username": "Geoff",
            "recent_activity": {
                "id_activity": 1,
                "description": "Login",
                "time": "2017-08-02 08:24:10"
            }
        },
        {
            "id": 3,
            "username": "Steve",
            "recent_activity": {
                "id_activity": 2,
                "description": "Edit Profile",
                "time": "2017-08-16 03:25:21"
            }
        },
    ]
}

However, I can't figure out how to actually sort and filter this data by clicking the column heads and typing in the search box. I'm trying to capture the search value (and other filtering options) in my endpoint with:

$search = isset($_POST["search[value]"]) ? $_POST["search[value]"] : null;
$username = isset($_POST['username']) ? $_POST["username"] : null;
$limit = isset($_POST['limit']) ? $_POST["limit"] : 50;
$offset = isset($_POST['offset']) ? $_POST["offset"] : 0;

QUESTIONS

1) How should I be retrieving the search value?

2) How do I figure out which column to sort by, or know if the user just wants to search on a single column?

3) If the API returns a number of objects greater than 5, the table displays all of them despite me setting the page limit to 5.

4) How can I debug a table, see what it's sending, have the API report back what it sees, etc? I tried the Datatables Debugger, but I have no idea how to use it. It just gives me a page saying I have no tables: https://debug.datatables.net/izepib

kthorngren

I'm not familiar with PHP coding, which I think you have above, but this doc explains how the server side process:
https://datatables.net/manual/server-side

Are you writing your own PHP code or trying to use the examples provided by Datatables?

I believe you can get the Datatables example PHP scripts here:
https://github.com/DataTables/DataTables/tree/master/examples/server_side/scripts

Look for the ssp.class.php script. You can either try using it or modifying for your own needs.

Its much less complicated if you can use client side processing. How many rows of data is in your DB? checkout this FAQ:
https://datatables.net/faqs/index#General-initialisation

Kevin

GoldenJoe

Goodness what a fast response on a holiday. You guys are amazing.

I read the serverside doc, but I don’t understand how the column-based parameters work. Is everything strictly by column index as declared?

I will dive deeper into the sample project. It’s a little confusing because I already have my endpoints written as part of an existing service, and ssp seems to be about building queries. I really just want to take DataTable’s parameters and convert them to something my API understands. I think there may be some way to do this with data()?

Like you said, client side may be an option...I think for something like user-submitted comments there could be millions of records eventually, and it would be good to at least know how to solve the problem.