Datatables and server side processing

Does anyone have any examples of server side processing with DataTables (and Plone)?

I want to show a very long table / list

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

1 Like

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 :slight_smile: )

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:

  1. The javascript loads the 'list' (all 30.000)
  2. The javascript 'adds' the html for the first page / batch
  3. 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:

  1. 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)
  2. With server side processing, the search or 'next page / batch' is sent to the browser
  3. 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 :wink:

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 )

Plone Foundation Code of Conduct