On almost every topic
jQuery DataTables and Alfresco

The jQuery library is a concise and easy to use Ajax library for rapid web development. It is also very usable to develop dashlets in Alfresco. When customizing Share the YUI library might be the prefered way to build dashlets, since it is the framework used to build Share, but compared to jQuery it is rather complex. Recently we used DataTables, a table plug-in for jQuery, to deliver a couple of Alfresco Explorer Dashlets. It is a an easy to use library with a lot of features. 

DataTables is designed and created by Allan Jardine and is dual licensed under the GPL v2 license or a BSD (3-point) license. Please make sure that you understand the licenses before you start using DataTables in your projects.

This tutorial assumes that you are familiar with Alfresco, JavaScript, HTML, JSON and preferably some XML. 

Download example code

The example code is available for download here.

Goals

The DataTables website contains a lot of examples to get you started. In this tutorial we will develop a table that enables the user to search for content stored in Alfresco using the keyword search web script that implements the OpenSearch standard. We will cover processing XML from the back-end, search, paging, showing and hiding columns and rendering column values to add custom markup. Finally we will add the ability to store the table’s state in a session cookie.

Initial setup

For this example I used a fresh install of Alfresco Enterprise 3.4.0, but you can also use the community version or an older Alfresco 3 release. The Alfresco service we will use for this example is the open search web script that is available out of the box. You can run it with a single keyword parameter and it will return an response based on the Atom feed protocol. You can give the keyword search a try using the following URL:

http://localhost:8080/alfresco/service/api/search/
  keyword.atom?q=document

This should return a response similar to this:

Open Search Feed

Take a look at the page source to see what the response data looks like. This will be helpful when we start processing the data in our table.

I created a ROOT folder under the webapps folder of my Tomcat distribution for my client-side page containing the table. You can also create an Alfresco Web Script.

Download the plug-in

The first step is to download the JavaScript library DataTables. Simply unpack the download under the ROOT folder. You will see a folder called media. It includes the required JavaScript and CSS files.

Create the file

The next step is to set up a basic HTML file for our client side table. We will start with a simple table listing the name of the documents returned by the OpenSearch Web Script. Create a file opensearch.html in the ROOT folder:

<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <title>jQuery DataTables and Alfresco OpenSearch Example</title>
  </head>
  <body id="dt_example">
    <div id="container">
      <h1>jQuery DataTables and Alfresco OpenSearch Example</h1>
      <div id="dynamic">
        <table class="display" id="example">
          <thead>
            <tr>
	      <th>Name</th>
            </tr>
	  </thead>
	  <tbody>
	    <tr>
	      <td class="dataTables_empty">Loading data from server</td>
	    </tr>
	  </tbody>
	</table>
      </div>
    </div>
  </body>
</html>

Add the libraries and stylesheets

The next step is to include the required JavaScript libraries and stylesheets. Add the following markup to the header of the page right under the title element:

<style type="text/css" title="currentStyle">
  @import "media/css/demo_page.css";
  @import "media/css/demo_table.css";
</style>
<script type="text/javascript" src="media/js/jquery.js"></script>
<script type="text/javascript" src="media/js/jquery.dataTables.js"></script>
<script type="text/javascript">
<!-- here we will write our client-side JavaScript -->
</script>

Initialize the DataTable

The JavaScript code for the table is pretty straight forward. Setting up the table is extensively covered in the documentation. Add the following lines to the script tag:

function fnGetJSONData( sSource, aoData, fnCallback ) {
  /* this function will execute the keyword search */
}

$(document).ready(function() {
  $('#example').dataTable( {
    "bServerSide": true,
    "sAjaxSource": "/alfresco/service/api/search/keyword.atom",
    "bSort": false,
    "bPaginate": false,
    "fnServerData": fnGetJSONData
  } );
} );

Implement the callback function

The final step is to write the function that prepares the request to the Alfresco back-end and populates the JSON array we will pass to our table. DataTables uses a very simple approach to populate the table. You simply provide a JSON object containing the column values and a couple of metadata fields. A typical JSON response looks like this:

{"sEcho":1,
"iTotalRecords":"10",
"iTotalDisplayRecords":"10",
"aaData":[
  ["localizable.ftl"],
  ["translatable.ftl"],
  ["doc_info.ftl"],
  ["notify_user_email.ftl"],
  ["emailbody-textplain.ftl"],
  ["my_docs.ftl"],
  ["general_example.ftl"],
  ["example test script.js"],
  ["show_audit.ftl"],
  ["emailbody-texthtml.ftl"]
]}

This is however not the response that is returned by the keyword search. Alfresco’s keyword search uses the Atom feed protocol as defined in the OpenSearch data format.

Luckily the DataTables site contains an example plug-in created by Garry Boyce that shows how to process the OpenSearch data format. The following lines of code provide a first implementation for our call to the Alfresco back-end.

function fnGetJSONData( sSource, aoData, fnCallback ) {
	  
  $.ajax( {
    "dataType": 'xml', 
    "type": "GET", 
    "url": sSource, 
    "data": {"q":"document"}, 
    "success": function (data,textStatus,xmlHttpRequest) {
      var jData = $( data );			
      var json = {"sEcho": 1,"aaData" : []};
      json.iTotalRecords = 
        jData.find("[nodeName='opensearch:totalResults']").text();
      json.iTotalDisplayRecords = json.iTotalRecords;			
      var items = jData.find("entry").each(function(){
        json.aaData.push([
          $(this).find("title").text()
        ]);
      });
      fnCallback(json);
    }
  } );					
}

The most complex part is the function that prepares the JSON object used to populate the table. The data returned by the back-end is encoded in XML, so we have to build the JSON object using the XML response data. What we need is a JSON object like we showed in the previous paragraph. This function first creates an initial JSON object in a variable called json:

var json = {"sEcho": 1,"aaData" : []};

Next the iTotalRecords and iTotalDisplayRecords items are added to the JSON object.

json.iTotalRecords =  jData.find("[nodeName='opensearch:totalResults']").text();
json.iTotalDisplayRecords = json.iTotalRecords;

The final step is to iterate over the entry elements in the XML response to add the rows to aaData. For each entry we add the title element.

var items = jData.find("entry").each(function(){
  json.aaData.push([
    $(this).find("title").text()
  ]);
});

Now if you open the file opensearch.html in your browser you should see a page similar to this:

OpenSearch DataTable Example

Add search

This example is not very useful since it does not allow users to enter search terms. It will always request documents that contain the keyword ‘document’. To enable the user to enter search terms we need to extend our function.

The first step is to collect the request information. It will contain any search parameters the user enters in the text box. The request parameters are stored in aoData. It contains a key value pair for each parameter:

[
  {"name":"sEcho","value":1},
  {"name":"iColumns","value":1},
  {"name":"sColumns","value":""},
  {"name":"iDisplayStart","value":0},
  {"name":"iDisplayLength","value":-1},
  {"name":"sSearch","value":""},
  {"name":"bRegex","value":false},
  {"name":"sSearch_0","value":""},
  {"name":"bRegex_0","value":false},
  {"name":"bSearchable_0","value":true}
]

When the user enters a search term, the value is stored with the key sSearch. To make it easier to work with request parameters we can first put all the parameters in a map:

var params = {};
for ( var i=0 ; i <aoData.length ; i++ ) {
  var entry = aoData[i];
  params[entry.name] = entry.value;
}

We can then check if the user entered a search term:

if (params["sSearch"] == undefined || params["sSearch"] == "") {
  /* no search term entered */
} else {
  /* search term entered */
}

If there are no search terms entered we return an array with no rows:

var json = {"sEcho": undefined,"aaData" : []};
json.iTotalRecords = 0;
json.iTotalDisplayRecords = 0;
fnCallback(json);

Otherwise we add the search terms to the request parameters we send to the Alfresco backend, just like we did with the static value. I also added the dynamic value for the sEcho parameter:

 $.ajax( {
  "dataType": 'xml', 
  "type": "GET", 
  "url": sSource, 
  "data": {"q":params["sSearch"]}, 
  "success": function ( data,textStatus,xmlHttpRequest ) {
    var jData = $( data );
    var json = {"sEcho": params["sEcho"],"aaData" : []};
    json.iTotalRecords = 
      jData.find("[nodeName='opensearch:totalResults']").text();
    json.iTotalDisplayRecords = json.iTotalRecords;			
    var items = jData.find("entry").each(function(){
      json.aaData.push([
        $(this).find("title").text()
      ]);
    });
    fnCallback(json)
  }
} );

When you open the file opensearch.html in your browser you will see that there is initially no data. Once you start entering search values, you will see the results displayed in the table.

Adding paging

The Alfresco keyword search back-end also supports paging. To add paging you need to add two parameters to the search request: a parameter ‘c’ for the amount of rows you want to retrieve and a parameter ‘p’ for the page within the result set. So to retrieve the first five rows for documents that contain the word ‘document’ we can send the following request:

http://localhost:8080/alfresco/service/api/
  search/keyword.atom?q=document&c=5&p=1

The DataTables implementation behaves slightly different when it comes to paging. In stead of providing a parameter for the page it will provide the count for the first row we want to display out of the total records. So if we want to display the next five rows, the pager provides us with a key iDisplayStart with value 6 and not a value 2 for the second page. We can fix this issue using the following calculation:

var start = simpleMap["iDisplayStart"]/simpleMap["iDisplayLength"] + 1;

Our updated Ajax request will now look like this:

var start = (params["iDisplayStart"]/params["iDisplayLength"]) + 1;
var length = params["iDisplayLength"];
var query = params["sSearch"];

$.ajax( {
  "dataType": 'xml', 
  "type": "GET", 
  "url": sSource, 
  "data": {"q":query,"c":length,"p":start},
  "success": function ( data,textStatus,xmlHttpRequest ) {
    var jData = $( data );
    var json = {"sEcho": params["sEcho"],"aaData" : []};
    json.iTotalRecords = 
      jData.find("[nodeName='opensearch:totalResults']").text();
    json.iTotalDisplayRecords = json.iTotalRecords;			
    var items = jData.find("entry").each(function(){
      json.aaData.push([
        $(this).find("title").text()
      ]);
    });
    fnCallback(json)
  }
} );

To show the paging in our table we need to update some initialization parameters:

$(document).ready(function() {
  $('#example').dataTable( {
    "bServerSide": true,
    "sAjaxSource": "/alfresco/service/api/search/keyword.atom",
    "bSort": false,
    "bPaginate": true,
    "sPaginationType": "full_numbers",
    "fnServerData": fnGetJSONData
  } );
} );

When you load the page in your browser you should now be able to page through the results.

DataTable

Setting a delay to reduce server load

What you will see when you use a tool like Firebug is that a request is submitted to the Alfresco server for every character we enter in the search field. To prevent this, we can add a delay. Developers Zygimantas Berziunas and Allan Jardine created a plug-in function called fnSetFilteringDelay() that provides this functionality. You can find the code on the DataTables website. I added the function as a separate JavaScript file in the media folder with the other JavaScript files and imported the file in opensearch.html:

<script type="text/javascript" src="media/js/jquery.setFilteringDelay.js"></script>

Once this is done, you can add the function to the table initialization code:

$(document).ready(function() {
  $('#example').dataTable( {
    "bServerSide": true,
    "sAjaxSource": "/alfresco/service/api/search/keyword.atom",
    "bSort": false,
    "bPaginate": true,
    "sPaginationType": "full_numbers",
    "fnServerData": fnGetJSONData
  } ).fnSetFilteringDelay(400);
} );

When you open your browser and use Firebug, you will see that the request to the back-end is not submitted for each character entered in the search field.

Adding columns

Now that we provided search and paging, we can add columns to our table to provide more information about the content we retrieve. To add the author for example, we need to add the value for the author to the JSON array:

var items = jData.find("entry").each(function(){
  json.aaData.push([
    $(this).find("title").text(),
    $(this).find("author").find("name").text()
  ]);
});

And we need to add a header to our table:

<thead>
  <tr>
    <th width="80%">Title</th>
    <th width="20%">Author</th>
  </tr>
</thead>

That is all we need to do in order to add a column.

Rendering columns

To complete our table we will add the icon for the type of document and a link to the document. Again the first step is to add the columns to our JSON array:

var items = jData.find("entry").each(function(){
  json.aaData.push([
    $(this).find("icon").text(),
    $(this).find("link").attr("href"),
    $(this).find("title").text(),
    $(this).find("author").find("name").text()
  ]);
});

To add the icon and the link to the name column we need to hide the columns with the icon reference and the link and we need to add a renderer that adds the markup for the icon image and the link. To do this add a parameter aoColumnDefs to the table initialization code:

"sPaginationType": "full_numbers",
"aoColumnDefs": [ 
{
  "fnRender": function ( oObj ) {
    return '<img src="' + oObj.aData[0] + 
      '" /> <a href="' + oObj.aData[1] + 
      '">' + oObj.aData[2] + '';
  },
  "aTargets": [ 2 ]
  },
  { "bVisible": false,  "aTargets": [ 0,1 ] 
}],
"fnServerData": fnGetJSONData

Even when you hide the columns, you need to add them to the table:

<thead>
  <tr>
    <th width="0%">Icon</th>
    <th width="0%">Link</th>
    <th width="80%">Title</th>
    <th width="20%">Author</th>
  </tr>
</thead>

When you open the file in your browser you should see a page similar to this:

OpenSearch DataTables

When you click on the title, the browser will show a preview of the file from the Alfresco backend.

Saving the state in a session cookie

Now when you return to the table, you have to resubmit your search and navigate to the page that you left when you clicked the link. To solve this we simply add the following parameter to the table initialization code:

"bStateSave": true,
"fnServerData": fnGetJSONData

This will save the state of the table in a session cookie.

Adding a style class to a table cell

This final example shows how you can add a style class to a specific table cell. We will add a new column displaying the score and if the score is greater than 0.5 we will display the score in red and otherwise we will display the score in gray.

The first step is to add the score to the JSON object used to populate the table:

var items = jData.find("entry").each(function(){
  json.aaData.push([
    $(this).find("icon").text(),
    $(this).find("link").attr("href"),
    $(this).find("title").text(),
    $(this).find("author").find("name").text(),
    $(this).find("[nodeName='relevance:score']").text()
  ]);
});

The next step is to add the column to the table:

<thead>
  <tr>
    <th width="0%">Icon</th>
    <th width="0%">Link</th>
    <th width="70%">Title</th>
    <th width="20%">Author</th>
    <th width="10%">Score</th>
  </tr>
</thead>

We will then add the classes to the stylesheet demo_table.css located in the media/css folder:

td.high {
    color: #FF0000;
}

td.low {
    color: #BEBEBE;
}

The final step is to add a new parameter called fnRowCallback to our table initialization code:

"fnRowCallback": function( nRow, aData, iDisplayIndex ) {
  if (parseFloat(aData[4].replace(',','.')) > Number(0.5)) {
    $('td:eq(2)', nRow).addClass('high');
  } else {
    $('td:eq(2)', nRow).addClass('low');
  }
  return nRow;
},

Note: Since I am using a Dutch system locale I had to replace the comma in the decimal numbers with a dot using a replace (aData[4].replace(',','.')).

The result will look similar to this:

Example 5

Conclusion

The jQuery DataTables plug-in provides powerful features to create tables that retrieve data from an Alfresco back-end. We used a standard out of the box Web Script to retrieve our data, but you can of course create your own back-end Web Scripts. You can also use CMIS to retrieve back-end data. You can use this approach to create Web Script pages, Alfresco dashlets or to add tables to your custom Spring Surf client.

We covered quite some features of the jQuery DataTables plug-in, but there is a lot more you can do. You can for example add sorting, style your table using ThemeRoller, add row and column highlighting, add additional search fields or submit data back to the server.

Read the follow up  tutorial

The follow up post jQuery DataTables, CMIS and Alfresco provides a similar approach to populate DataTables using the CMIS standard.

  1. marsbard reblogged this from bpeters
  2. pasieki-org-pl reblogged this from bpeters
  3. psdtohtmlshop reblogged this from bpeters
  4. healthnewsbytehost reblogged this from bpeters
  5. trampoline6t reblogged this from bpeters
  6. xn----slbefavdc9aecr2ax8cfbiip9g reblogged this from bpeters
  7. web-scripts reblogged this from bpeters
  8. bpeters posted this