Does anyone have any examples of server side processing with DataTables (and Plone)?
I want to show a very long table / list
Our BDA Shop uses server side datatables here:
and below in the file
Yes, but in a customer project. I will try to extract it tomorrow. We also use c.collectionfilter with a specific branch to not reload the whole content area, but just send a JS event: https://github.com/collective/collective.collectionfilter/tree/notifyjsonly
Thanks a lot. Looks interesting
Looking at this, it looks like this could be useful for some behaviour / add-on. Will see if I get the time tp do it.
Something like "CSV (or json ? ) field and a DataTables view.
I did a test with (just) getting the data from view.py.
This works, maybe a little bit slow. Not sure if that is "DataTables itself', will try to find out.
I tried with both CSV and data grid field (of course, large datagridfields probably brings the server down if you try to edit them)
Does anyone know 'how much data it takes to be faster / better to do server side processing (how can I measure this)
I paste my "very basic code", in case someone should ever need it:
<table class="sortable" />
<table class="sortable pat-tablesorter listing">
<thead>
<tr>
<th>One</th>
<th>Two</th>
<th>Etc</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.22/css/jquery.dataTables.min.csss">
$(document).ready(function() {
$('.sortable').DataTable( {
"processing": true,
"pageLength": 100,
"lengthMenu": [ [100, 250, 500, 1000, -1], [100, 250, 500, 1000, "All (extremely slow)"] ],
data: ${python: view.processCSV()},
// add column definitions to map to the table
// should find these instead of hard-code
"columns": [
{data: "one"},
{data: "two"},
{data: "etc"},
]
} );
});
UPDATE:
And a view.py similar to
# -*- coding: utf-8 -*-
from Products.Five.browser import BrowserView
#for the csv import
import StringIO
import csv
def processCSV(self):
""" Just a test
"""
table = []
data=self.context.csv_file.data
reader = csv.reader(
StringIO.StringIO(data),
delimiter=';',
dialect='excel',
quotechar='"'
)
header = reader.next()
for row in reader:
# should not hardcode this, fix later (for x in row, maybe)
table.append({'one': row[0], 'two': row[1], 'etc': row[2]})
#probably make a check if data exist, then
return table
@espenmn, the view is based on the plone.app.contenttypes.browser.collection.CollectionView, which was required for c.collectionfilter to work at that time.
BOOLEAN_INDICES = ["boolean_index_one", "boolean_index_two"]
class ProductList(CollectionView):
"""A collection view for Products."""
@property
@memoize
def wf_tool(self):
return api.portal.get_tool(name="portal_workflow")
@memoize
def workflow_state_title(self, review_state, portal_type):
return self.wf_tool.getTitleForStateOnType(review_state, portal_type)
def json_results(self): # noqa: C901
query = self.request.form
custom_query = {}
# Handle c.collectionfilter params
if "customQuery" in query:
custom_query = urlparse.parse_qs(query.get("customQuery"))
if custom_query.get("collectionfilter"):
del custom_query["collectionfilter"]
# Convert boolean indices into correct format
for index in BOOLEAN_INDICES:
if custom_query.get(index) is not None:
val = custom_query.get(index)[0]
if val == "False":
custom_query[index] = False
elif val == "True":
custom_query[index] = True
searchable_text = query.get("search[value]") or None
if searchable_text:
if not searchable_text.endswith("*"):
searchable_text = searchable_text + "*"
custom_query["SearchableText"] = searchable_text
# Sorting from datatables
sort_on = query["columns[" + query["order[0][column]"] + "][name]"]
reverse = True if query["order[0][dir]"] == "desc" else False
# Datatables allows selecting a batch size
params = {
"b_size": int(query.get("length", 25)),
"b_start": int(query.get("start", 0)),
"sort_on": sort_on,
}
if reverse:
custom_query["sort_order"] = "reverse"
params["custom_query"] = custom_query
results = []
error = None
try:
batch = self.results(**params)
except ParseTree.ParseError:
error = "Parse error"
batch = []
for item in batch:
# Prepare your results data if required
# ...
# Get a translated workflow state title
review_state_title = translate(
self.workflow_state_title(item.review_state(), item.portal_type),
context=self.request,
domain="plone",
)
results.append(
{
"product_title": item.Title(),
"product_url": item.getURL(),
# More fields dependend on your need
# ...
"review_state": item.review_state(),
"review_state_title": review_state_title,
"modified": item.modified.strftime("%d.%m.%Y"),
}
)
# Ensure JSON response is not themed
self.request.response.setHeader("X-Theme-Disabled", "True")
self.request.response.setHeader(
"Content-Type", "application/json; charset=utf-8"
)
if error is not None:
return json.dumps({"error": error})
return json.dumps(
{
"draw": query.get("draw", 1),
"recordsTotal": batch.sequence_length,
"recordsFiltered": batch.sequence_length,
"data": results,
}
)
def querystring(self):
return self.request.get("QUERY_STRING") or ""
The corresponding template:
<html xmlns="http://www.w3.org/1999/xhtml"
xmlns:metal="http://xml.zope.org/namespaces/metal"
xmlns:tal="http://xml.zope.org/namespaces/tal"
xmlns:i18n="http://xml.zope.org/namespaces/i18n"
i18n:domain="example.product"
metal:use-macro="context/main_template/macros/master">
<metal:content-core fill-slot="content-core">
<metal:block define-macro="content-core"
tal:define="lang python:context.restrictedTraverse('@@plone_portal_state').language()">
<script type="text/javascript" charset="utf-8">
plone_define = define;
define = undefined;
</script>
<script tal:content="structure python: 'var products_url = \'%s/@@product_list_json\'' % context.absolute_url()" />
<script tal:content="structure python: 'var customQuery = \'%s\'' % view.querystring()" />
<script tal:condition="python: lang=='en'">var lang = 'en'; var language_url = 'https://cdn.datatables.net/plug-ins/1.10.19/i18n/English.json'</script>
<script tal:condition="python: lang=='de'">var lang = 'de'; var language_url = 'https://cdn.datatables.net/plug-ins/1.10.19/i18n/German.json'</script>
<script tal:condition="python: lang=='it'">var lang = 'it'; var language_url = 'https://cdn.datatables.net/plug-ins/1.10.19/i18n/Italian.json'</script>
<script type="text/javascript" src="https://cdn.datatables.net/responsive/2.2.3/js/dataTables.responsive.min.js"></script></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/responsive/2.2.3/css/responsive.dataTables.min.css" />
<script type="text/javascript" charset="utf-8">
$(document).ready(function() {
$(document).on('collectionfilter:notifyReload', '.pat-collectionfilter', function (e, data) {
var portlethash = $(e.target).parent().attr('data-portlethash');
if(data.target === portlethash) {
var filterUrl = data.targetFilterURL;
customQuery = filterUrl.substring(filterUrl.indexOf('?') + 1);
datatable.ajax.reload();
}
});
var datatable = $('#results_table').DataTable({
"pageLength": 25,
"processing": true,
"serverSide": true,
"stateSave": true,
"ajax": {
"url": products_url,
"type": "POST",
"data": function(d) {
return $.extend({}, d, {
"customQuery": customQuery
});
}
},
"columnDefs": [
{
"targets": 0,
"render": function (data, type, row, meta) {
return '<a i18n:ignore href="'+ row.product_url +'">' + data + ' </a>';
}
},
// more similar column defs
// ...
],
"columns": [
{
"data": "product_title",
"name": "sortable_title",
},
{
"data": "review_state_title",
"name": "sortable_review_state"
},
{
"data": "modified",
"name": "modified"
},
],
"language": {
"url": language_url
},
"createdRow": function (row, data, index) {
// add the review state class to the column containing the review state
$('td', row).eq(1).addClass('state-' + data['review_state']);
}
});
});
</script>
<metal:listingmacro define-macro="listing">
<tal:results define="items view/results;">
<table class="display responsive wrap" style="width: 100%;" id="results_table" summary="Show a list of products." i18n:attributes="summary;">
<thead>
<tr>
<th i18n:translate="" data-priority="1">Product Name</th>
<th i18n:translate="" data-priority="5" i18n:domain="plone">Review state</th>
<th i18n:translate="" data-priority="4" i18n:domain="plone">Last modified</th>
</tr>
</thead>
</table>
</tal:results>
</metal:listingmacro>
</metal:block>
</metal:content-core>
</html>
EDIT: here the corresponding ZCML decalrations:
<!-- A collection view for Products. -->
<browser:page
class=".products.ProductList"
for="plone.app.contenttypes.behaviors.collection.ISyndicatableCollection"
layer="example.product.interfaces.ICustomLayer"
menu="plone_displayviews"
name="product_list"
permission="zope2.View"
template="templates/product_list.pt"
title="Product List"
/>
<browser:page
class=".products.ProductList"
for="plone.app.contenttypes.behaviors.collection.ISyndicatableCollection"
layer="example.product.interfaces.ICustomLayer"
name="product_list_json"
permission="zope2.View"
attribute="json_results"
/>
1 Like
Sure. We started with the regular datatables, but with around 5000 products the view was getting slow (of course this depends on the amount of columns and complexity of cells you have). We experienced load times of 20 seconds and more.
Now we have around 30000 products and the load time for the datatable is in milliseconds range.
I use datatables with for a listing with many timeslots. from 1000 slots (every slot is a row) and more the processiing of the table is slow and the enduser wait a little bit. i would implement an ajax / serverside handling if the number of rows over 1000.
That sounds extremely much.
Did you do all the 'table stuff' with TAL ?
My test takes maximum 2 sec. with 30.000 lines. You can see it here if the demo site is up ( http://xweb14d.plana.dk:8052/Plone/test-datatables )
… to be continued ( need to work first 
)
That depends on the amount and complexity of data you use. In our case it was a catalog search with just using brains. But if you have 30000 lines of HTML rows that is going to be slow to render. The code is at least 2 years old, running on Plone 5.1.x.
BTW, your example doesn't work (when not logged in):
I don't think I have 30000 lines of html when using the "data" option.
will take a look at it
Datatables renders regular HTML (from your JS data) which is then parsed/styled/presented to you by the browser.
Edit:
Using data is just a different approach for telling Datatables where to get the data from.
Of course 30000 when you use the regular approach, otherwise you get the number of rows according to your batch settings.
That was with the regular approach, not the server-side processing. And again, it also depends on the structure/markup/presentation of your data, how many columns etc.
Are you sure about this:
To me it looks like if you use 'data', the javascript only 'draws' the html for the first page.
I thought it worked like this:
- The javascript loads the 'list' (all 30.000)
- The javascript 'adds' the html for the first page / batch
- When searching or moving to another 'page /batch', the the filtering is done in javascript and then the html rows are updated.
So I thought that:
- With javascript you have to send the whole list (all 30.000 items) while if you process in python you only send the first (in my case 500)
- With server side processing, the search or 'next page / batch' is sent to the browser
- Without server side, the searching / filtering is done in javascript, so it will use resources and memory on the client, but it will not have to resend anything.
I am working on something else now (a book to be printed), but I will try to look into this later.
Yes, you are right, but 30000 items in JS is still a bit too much 
The DataTables js was in 'logged in bundle', I think it works now:
http://test.medialog.no/test-datatables
PS: The demo site will probably be removed in some weeks.
No, same error.
Think it has (or had) to do with: Moving js (or/and css) to another bundle
http://test.medialog.no/test-datatables/
until I get to fix it, it might work as user ( datatableman / datatablepass )
In case I or someone else needs this in the future, I have started working on an add-on
I am currently a bit stuck with overriding the 'file' widget', so for now I am using two fields (since I want to keep the csv file so users 'know where to find it when they need it' and)
So, the processing is done in javascript, not server side yet. Feel free to help if you need this.
NOTE: I have a bug if any of the table headers contains '.', that row will not render
EDIT: Install the add-on and add the behaviour to your content type first.
Slightly off the original topic, but:
Does google 'find' all you products.
For my setup, google only see the first 'page' (100 items), so the other 58700 items will not appear in google
No, Google won’t find them, it’s an intranet
But if it’s public it should. All items are listed in the sitemap. And I can’t remember datatables rewriting the links so they are useless.