Sunday 1st May, 2011

Extended data source options with DataTables

Update: This blog post was written before the release of DataTables 1.10, and therefore does not make use of the new API or features used by the latest DataTables releases, in particular the columns.render and columns.data options. This post is left for historical reference; please refer to the manual for information on the current release of DataTables.

Welcome to the new DataTables blog! I would like to use this section of the site to introduce various topics which are useful for DataTables and give tutorials about how to integrate some of the more advanced aspects of DataTables into your site.

For my first post, I'd like to introduce one of the new features in the forthcoming 1.8 release of DataTables (which is currently in beta: available here) - that of extended data source options. This new feature in DataTables 1.8 relaxes the data formatting constraints that DataTables places on the data source. In previous releases of DataTables the following data structure is expected for server-side processing or Ajax sourced data:

[
    [ "row 1, cell 1", "row 1, cell 2", "row 1, cell 3" ],
    [ "row 2, cell 2", "row 2, cell 2", "row 2, cell 3" ]
]

This is an array of arrays, with (exactly) one element given for each of the cells in the table. Any deviations from this structure, or extra elements in the arrays, would result in an error being given. While this is adequate for many tables, and server-side scripts flexible enough to allow this formatting to be generated without significant modification, it is not particularly flexible.

To increase the flexibility of the data source, you may wish to return an array of objects, like this:

[
    { "cell1": "row 1, cell 1",
      "cell2": "row 1, cell 2",
      "cell3": "row 1, cell 3" 
    },
    { "cell1": "row 2, cell 1",
      "cell2": "row 2, cell 2",
      "cell3": "row 2, cell 3" 
    }
]

In this manner you could pass back extra information which is not needed for rendering the table directly. This could be useful for showing and hiding details about a particular row, based on information that is not visible in the table. Previously this had to be done with information in hidden columns, or with an extra Ajax request. This ability to read information from objects is what is introduced in DataTables 1.8, through the use of the mData initialisation option for aoColumns.

To make use of the array of objects shown in the example above, using the mData option, DataTables could be initialised like this:

$('#example').dataTable( {
    "aoColumns": [
        { "mData": "cell1" },
        { "mData": "cell2" },
        { "mData": "cell3" }
    ]
} );

In this code, I use aoColumns to create an initialisation specification for each of the columns in the table (in this case three columns) - this simply tells DataTables how to treat each of the columns, using these values in place of the default values. mData is then set to the name of the property that each want from each row's object for the target column. Note that this makes the order of the column data in the source objects completely irrelevant!

Default behaviour

By default the mData is an integer value, reading from the array index of the value as the column index (i.e. if it is the second column then array index 1 is the data source). This replicates the behaviour of previous releases of DataTables and how they handle data. As such this new feature is fully backwards compatible. You can set mData to be an integer if you wish, which can be useful for reordering data from the original source, if it gives you arrays. Alternatively, as shown in the example above you can set mData to be a string, in which case DataTables will read that property from the source object.

Deep data property reading

The new mData provides a lot more flexibility than simply reading information from flat objects. Using standard Javascript dotted object notation you can read deeply nested properties and arrays. For example, you might have a data source which looks like this:

[
    { "server": "Orion",
      "location": "London",
      "details": [
            "GMT",
            "10.0.0.1"
        ]
    },
    { "server": "Saturn",
      "location": "New York",
      "details": [
            "EST",
            "10.0.1.1"
        ]
    }
]

In this case there is a nested array where the first element is the timezone that the server detailed is located in and the second element is the server's IP address. To show this in a four column DataTable you could use the following DataTables initialisation:

$('#example').dataTable( {
    "aoColumns": [
        { "mData": "server" },
        { "mData": "location" },
        { "mData": "details.0" },
        { "mData": "details.1" }
    ]
} );

In this case you can see that the third column refers to the first element of the details array (i.e. in Javascript you would write row['details'][0]. There are no limits to how deeply nested data can be (although obviously the more deeply nested the more processing required - DataTables makes optimisations for less than three levels of nesting to ensure they are as fast as possible).

Further examples and discussion

There are a number of examples of how this enhanced data handling can be used in the DataTables package:

I hope you find this post to be useful in your use of DataTables! There is a thread in the forum for comments and discussion on this post.