jQuery Datatables in frontend with serverside processing?

[hheyne]

Hello,

has anybody here done this with processwire:

https://datatables.net/examples/server_side/

I want to use this solution because I have to work with a huge amount of data.

TIA

Henning

[szabesz]

Hi,

Maybe you can use Soma's JqueryDataTables module as a starting point:

http://modules.processwire.com/modules/jquery-data-tables/

[Macrura]

@Henning,

How much data, in terms of rows are you needing to display?

AFAIK you'd just need to write a function to return the JSON needed by the ajax call from the datatables, using the API.

the SSP examples probably won't help because they are setup for MySQL;

I probably would have sorted this out by now, but i have yet to have a need for more than 500 rows, and at that level, inline JSON performs the best, in terms of page load;

[hheyne]

@szabesz: As far as I know the example doesn't use serverside scripts.

@Macrura: The amount of rows are around 1.000 - 10.000. Actually my problem is that the customer wants me to fetch extra informations which are located under the fetched pages. So I have to fetch 1.000 Pages (instead of 100 which are displayed) to get the whole data for the customer. Maybe it would be helpful if I make more use of MarkupCache in this areas ... but in this case the whole data must also be fetched ... not so often but at least once. I could delete the cache if some of the pages changes.

I looked at the SSP-example ... has somebody rewritten the SSP-example for processwire?

[Peter Knight]

Hello,

has anybody here done this with processwire:

https://datatables.net/examples/server_side/

I want to use this solution because I have to work with a huge amount of data.

TIA

Henning

I have a project running DataTables using a different CMS.

Coincidentally, yesterday I started rebuilding it in ProcessWire or at least doing a proof of concept with a small amount of data.

Currently I'm just populating the DataTables with a regular PW selector and foreach statement.

Do I get the feeling that long-term that's no fast enough?

On a side note, have you tried using ListerPro in the admin with multiple columns? That works very quickly albeit backend only.

[hheyne]

@Peter Knight: Yes I tried ListerPro in the backend. Very great tool! Unfortunately I need something like jQuery Datatables in the frontend ;-)

I started yesterday after hours of frustrating „try by error” with serverside jQ DT to implement it with the „foreach” approach to show the customer something. After the first „beta-release” in June I hope to have the time to rewrite the SSP stuff for processwire. Maybe someone else had done this so far in the meantime ;-) This would be a great helper to work with huge amounts of data in the frontend. The customers like the usabilty of jQ DT in the frontend.

[Peter Knight]

I looked at the SSP-example ... has somebody rewritten the SSP-example for processwire?

I'll probably look at this today and try a demo.

On this page, under the tab called "Server Side Script", it *seems* like we just need to add the proper database credentials and database columns etc or am I overlooking something crucial?

[hheyne]

Wow, that sounds promising ;-)

This could be possible. I thought about a solution with PW-Selectors instead of native PDO-MySQL-Queries. You must also look into the ssp.class.php. This file is included in the server-processing.php. All the stuff can be found in the download-file of the actual DT version.

[kongondo]

On this page, under the tab called "Server Side Script", it *seems* like we just need to add the proper database credentials and database columns etc or am I overlooking something crucial?

I wouldn't touch that if I were you. No need to. ProcessWire already has got you covered. You could probably get away with less than 10 lines of code..Something as simple as...OK...before that, let's break this down.

Server-side processing: is probably easier than client-side processing. The former is easier (IMO), because PW has got you covered...Throw some PW API in your template file and you are sorted. You only need to send back data in the format requested by the client, in this case DataTables is the client. 

Client-side: would have been a bit more difficult (for me...because I find PHP easier than JavaScript  ). However, in this particular case, we are covered. DataTables does all the heavy lifting. All we need to do is to give it data in the format and structure it has requested.

Requesting data: First, the JavaScript needed to request data (i.e. the client requesting data from the server)..Straight from the DataTables example

$(document).ready(function() {
    $('#example').DataTable( {// the ID of your HTML table
        "processing": true,
        "serverSide": true,
        "ajax": "scripts/server_processing.php"// this is the only thing to change RE ProcessWire(template file)
    } );
} );

Nothing fancy in that code. The value passed to the ajax property is the important thing here. In ProcessWire, we won't be able to access a PHP file directly like that (see forum posts about this). We have two choices. Either, post to self (./) - notice the dot, or post to an existing PW page. So...

$(document).ready(function() {
    $('#example').DataTable( {
        "processing": true,
        "serverSide": true,
        "ajax": "./"// posting to self (i.e. the current page)
        //"ajax": "/ajax-handler/"// posting to a ProcessWire page titled Ajax Handler that lives off the root
    } );
} );

Process ajax request: In the template file of the 'page' or the 'ajax-handler' page, depending on the value set to 'ajax' in the JS above, you will have code like so, ready to receive ajax requests..@see

if($config->ajax) {
 // here we are listening to ajax requests sent to this page
 /* 1. listen (could be POST or GET. in the DataTables example, it is using GET
    2. Check and sanitize required parameters
    3. Kosher? Send back data in the format requested, i.e. JSON. Otherwise, ignore, show error or tell them to take a hike 

 */

}

// output HTML as usual => e.g. @see the HTML tab in the DataTables SSP example

OK, the fun part. Fire-up Firebug and go to the 'Console' Tab. Visit the DataTables SSP page and watch the Console. Have a look at:

Params tab: DataTables has sent a long request. Most of the stuff we will not need. The key here is to match the parameters to their corresponding ProcessWire terms, i.e. limit, sort and start. That's all we need. This is basically pagination. So, let's match stuff we need for pagination...

DT => PW

start => start

length => limit

sort

// @note: index-based naming, where 0=first DataTables table column.

// In this case, the value is 5 (i.e. the last column in the SSP example = salary)

order[0][column] 5  => sort (sort will correspond to whatever property in your selector, e.g. title, some_text_field, etc)

order[0][dir] asc => sort order in ProcessWire. if asc, do nothing, if desc then sort becomes -sort

So, we get our data as normal in ProcessWire. Note, this code goes within the if($config->ajax){} condition...

// @note: here you sanitize inputs + could do some other logic, e.g. check if input present, etc
$start = (int) $input->get->start;
$limit = (int) $input->get->length;
$sort = $sanitizer->name(?)// @this is your homework; how to get to the order[0][column] and order[0][dir] values. ;-) {dir here is direction, btw}

$data = $pages->find("template=basic-page, start=$start, limit=$limit, sort=$sort");

if(count($data)) // {
// need to send back data with some extras to DataTables
// @hint: have a look at the structure of the JSON.
// @continued below...

}
else //error message, nothing found

Next, have a look at either the 'Response' or 'JSON' tabs. That is what the server has sent back. The most important thing to note is that that was originally an array on the server (built from our $data above with some extras...). Let's build this next.

if($config->ajax) {
// @note: this is built from the $data above
/*
 @note: as per the JSON tab in Firebug we need to send back 4 'things' to DataTables.

1. draw (int): I thought corresponds to the page number but seems to increment: I'll let you find out 
2. recordsTotal: Number of records found
3. recordsFiltered: I haven't checked what this is
4. data: The ProcessWire find results

*/

 // to get the total number of records:
 $total	= $data->getTotal();

 $dataDT = array();// we'll send this back to DataTables as JSON

 $data['draw'] = $whateverDrawIs;// could be $limit = $data->getLimit();
 $data['recordsTotal']  = $total;
 $data['recordsFiltered'] = $whateverThisMeans;

 // prepare values to send back that match your DataTables table headers
 foreach ($data as $d) {
   $dataDT[] = array($d->title, $d->name, $d->id, $d->parent->title, $d->template->name);// each record
 }

 // send data back to client in JSON format (@see the JSON tab in Firebug)
 header("Content-type: application/json");
 echo json_encode($dataDT);

}// end if ajax

And that's it

Written quickly in browser, got carried away...there could be errors, blah blah, the usual disclaimers 

Edited May 11, 2016 by kongondo
typos

[Peter Knight]

Great reading- thanks Kongondo.

So if I'm populating DataTables with the following, this is simply called Server Side processing you refer to?

<table id="datatables-manage-cows" class="display">
        <thead>
            <tr>
                <th>Date</th>
                <th>Time</th>
                <th>Description</th>
            </tr>
        </thead>
        <tbody>
        
      
  		
        <?php
	$items = $pages->find("template=a-cow, sort=-date,sort=-time");
	foreach ($items as $item){
	echo"<tr>
		<td>{$item->cow_date}</td>
                <td>{$item->cow_time}</td>
                <td>{$item->cow_description}</td>
	</tr>
	";
	}?>
      
            
        </tbody>
        </table>

[kongondo]

Erm...not quite. Server-side Processing is what ProcessWire (in this case; could be whatever technology or CMS on your server) does (finding the requested records, sanitizing inputs, ordering records and sending stuff back to the browser/client). Client-side Processing is done by DataTables. It has requested JSON, you send it back JSON (not sure, maybe it can also handle XML but JSON is way easier and cooler). Once you send it that back, don't try to help it ...

This is wrong: Let DataTables deal with those cows without your help client-side

<?php
	$items = $pages->find("template=a-cow, sort=-date,sort=-time");
	foreach ($items as $item){
	echo"<tr>
		<td>{$item->cow_date}</td>
                <td>{$item->cow_time}</td>
                <td>{$item->cow_description}</td>
	</tr>
	";
	}?>

Just set up the empty table as shown in the DataTables SSP example and it will do the rest. It will populate that raw HTML table (what the docs in that SSP page mean by enhancing the raw HTML table). To be precise, here are the steps...

1. Someone visits your web page. DataTables sends an ajax request to the server that John Doe wants to view this table. Maybe John Doe doesn't want to view the whole table; he only wants to see 10 cows (records) at a time. That's the pagination in the DataTables. Also, with every click of the 'next' or equivalent buttons, DataTables sends requests to the server. I might add here as well, but I haven't checked, maybe DataTables is capable of requesting 2 or 3 pages in advance, get them ready for John Doe when he wants to view them
2. The server technology, in this case ProcessWire, receives and processes the request sent by DataTables. Request could be denied or accepted. It all depends on how you have set up the processing. E.g., we could ignore John Doe unless he is logged in. If accepting the request, ProcessWire sends back data in the format and structure requested by DataTables. If you send it back in any other format (e.g. send back normal HTML or some normal string), DataTables will not understand it and will most likely throw a fit...OK an error...and John Doe will not be pleased...I can tell you that for free . OK, so you send back JSON in this case
3. DataTables is happy to receive your JSON back. It understands JSON and knows what to do with it. It quickly populates (aka enhances) the raw HTML table with the data sent back using ajax magic. If you structured your ProcessWire array (and hence the subsequent JSON) correctly, data should properly match their columns...otherwise John Doe...you know the rest 

[kongondo]

Quick example 

<?php
// at the very top of your template file
 if($config->ajax){

// the ProcessWire code in my example above

exit;

}

?>
<table id="example" class="display" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>First name</th>
                <th>Last name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>First name</th>
                <th>Last name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </tfoot>
    </table>

Edited May 11, 2016 by kongondo
clarification

[Soma]

Hi,

Maybe you can use Soma's JqueryDataTables module as a starting point:

http://modules.processwire.com/modules/jquery-data-tables/

That's the jQueryDataTable plugin loaded in PW for easy use, so not every module that needs jQueryDataTable would have to include it.

What you wanted was this earlier ajax implementation in PW I did before that: 

http://modules.processwire.com/modules/data-table/

[hheyne]

@kongondo: Thanks. Great explanation. I'll give it a try and report. Yesterday my mind had gone away :-(

[hheyne]

I just saw that there will be PW 3.0.19 on the way with $pages->findMany() ... I think this should fix the problem. If I had known about this new feature 

http://processwire.com/blog/posts/find-and-iterate-many-pages-at-once/

[Macrura]

just a quick reply with some comparisons here also since i have DT running on several sites now...

- HTML table (a.k.a DOM)

in this case the HTML is already on the page, but DataTables needs to read this HTML table and completely process it, remove it from the DOM, and render it.

For any table with a lot of rows or columns, this is a bad option.

- JSON Object

Instead of using an HTML table, you can spit out the table info as JSON on the page somewhere in a var. Since this is the native language of DT, this is very, fast, for any table data where the total object is around 1MB or less. Still with 1MB you can have a significant amount of rows/columns/markup in your JSON.

Also, if you use $cache,or ProCache, then the page load is very fast, and the DT rendering is almost instantaneous.

- AJAX

Ajax is almost the same as JSON object above but the JSON data lives on a separate URL, like on a service page. Still the JSON object is the full object, and even cached, this method is slower than inline JSON object because then the user is requesting 2 pages. This only makes sense when you have JSON that is probably more than 1MB and you don't want to have a large page load on the page that shows the table. So you have the page load fast, but the user has to wait a while for the server to respond to the ajax, and then for DT to process all of that JSON.

Datatables only makes 1 call on the ajax to get the data, so this is why you have to weigh AJAX vs. JSON object and do testing; In all of my tests, JSON object was the fastest and i'm dealing with about 1/2MB size JSON object.

- SSP

This is completely different than the above, because in this case the server needs to deliver the records only as they are needed to be viewed by the user.

Kongondo's example above would be a great way to get into this. It will probably be slower than JSON Object when dealing with small data sets, but for anything significant this is the best and only way to do it.

I also wonder how much pages->findMany will make this work better; In the case of SSP, you are always needing to paginate from the server, i guess the findMany would allow you to make less pages->find and thus make the table appear more responsive

[MindFull]

I've had to use DT for some rather large page results before (1000+) because it was the requirement of the customer. kongondo did a great job showing how easy it is to use PW alongside DT. Sometimes, the pages might need to be fetched with real time results and without reloading the current page when doing searches. I thought I’d mention the good old legacy format that DataTables still supports because I prefer it over the new API’s requirement to issue column information when using PW. Here's how a simple SSP file might look like using the legacy format:

// iDisplayStart helps in pagination as it keeps track of what record to start on when displaying results.
$iDisplayStart = intval($_GET['iDisplayStart']);
// iDisplayLength is how many records are displayed on the screen at once based on the 
// selection of the Records select box
$iDisplayLength = intval($_GET['iDisplayLength']);
// Start with an empty response array
$response = array();
// Get some pages. Limit results to whatever
$rows = $pages->find("template={$someTemplate},limit=1000");
// Create the response array that DataTables needs
foreach ($rows as $row) {
   // Get fields as needed
   $name = $row->name;
   $comments = $row->comments;

   // sSearch is the string typed into the search box by a user
   // Assuming searches are case-insensitive, make the search string and searchable field lowercase
   $sSearch = strtolower($_GET['sSearch']);
   // User has typed something so the results need to be filtered by hits
   if($sSearch != '') {
        // The search has found a hit in this record
        if(strpos(strtolower($comments), $sSearch)){
            // Add the hit to the response 
            $response[] = array(
                $name,
                $comment;
        }
    }
    // User has not typed anything in the search bar so return all results
    elseif($sSearch == ''){
        $response[] = array(
            $name,
            $comment
        );
    }
}
// Now that we might have results, send it back to the requesting page
if(!empty($response)) {
    $results = array(
        // sEcho refers to the page of pagination that is being viewed
        "sEcho" => intval($_GET['sEcho']),
        // Number of pages returned in total
        "iTotalRecords" => count($rows),
        // Number of filtered pages found based on search
        "iTotalDisplayRecords" => count($response),
        // Use array_slice for partition the result for pagination 
        // aaData is the array that DataTables uses for data that needs to be displayed
        "aaData" => array_slice($response, $iDisplayStart, $iDisplayLength)
    );
    // Finally send the results
    echo json_encode($results);
}

Obviously the _GET variables would need sanitizing and such. In addition, the old bDeferRender option for DT is good to use when loading large sets of data into the table since it will only the needed visible rows will be drawn at any given time.

[hheyne]

Hello everyone,

I made it. Thank you very much for the detailed informations above. Actually I have a new project with 5.000 or more datasets. I tried the three solutions. Native DT, Ajax DT ans SSPDT. Has anyone some tricks to speed up the generation of the data in SSP. It tooks around 4 Seconds to generate the data (10.000 records). I think it should be faster on the real server which will have 128GB of RAM with SSD. The testserver has only 4GB RAM and SSD on a virtual host.