You are here: System » JQDataTablesPlugin

JQDataTablesPlugin

13 Jun 2016 - 19:11 | Version 1 |
JQuery based progressive enhancement of tables

Overview

This plugin implements a widget to enhance normal HTML tables with extra navigation features such as sorting, filtering and paginating through datasets. The %DATATABLE macro is specifically tailored towards datasets created on the base of DataForms. It lets you easily compile an overview of data of that kind stored in a web as well as select specific records for further use in wiki apps.

JQDataTablesPlugin can be used in two distinct ways:

  • by means of the %DATATABLE macro
  • by means of HTML5 and JSON to enhance HTML tables directly

%DATATABLE takes a pointer to a DataForm definition and then creates a table based on the known formfields in that definition to create columns of a kind matching the formfield types. It then uses a connector fetch the data from the backend. There are several kinds of connectors available at the moment based on the additional functionality installed on your Foswiki:

  • search: this one uses the normal native search feature of Foswiki also used by the %SEARCH macro
  • dbcache: this one uses DBCachePlugin which is implementing a faster still native search, also available via its %DBQUERY macro
  • solr: this connector uses the SolrPlugin which uses a Solr fulltext search engine integrated into Foswiki.

A default connector can be defined as required. Note however, that even though %DATATGABLE tries to hide the differences of available connectors behind the scene, connectors will behave differently, i.e. when it comes to filtering. Also, performance is vastly different with the search connector being the slowest one. It is strongly recommended that you at least install DBCachePlugin for acceptable performance in every-day usage.

DATATABLE

Using a %DATATABLE you can query a set of topics and display their structured data in a dynamic table. Results are best when querying structured data attached to your topics as each formfield defined in the DataForm directly correlates to a column in the generated table. In this case you may specify the form definition in the form parameter and formfields in the columns parameter. The %DATATABLE will then use this information to properly display formfield values as well as sort columns along the technical representation. For example a date formfield will properly be displayed according to your locale's date format while sorting by date uses date values represented as epoch seconds.

Syntax: %DATATABLE{"<query>" parameter="..." parameter="..." ...}%

Parameter Description Default
<query>, query specify a search query to filter rows on the server-side; NOTE: the query language might vary depending on the connector being used, e.g. dbcache vs search vs solr  
connector connector to be used to fetch data dynamically from the backend; possible values are search (using Foswiki's native %SEARCH), dbcache (needs Foswiki:Extensions/DBCachePlugin), solr (needs Foswiki:Extensions/SolrPlugin), additional connectors may be implemented by plugins; NOTE: for general-purpose search dbcache is the best choice defined in $Foswiki::cfg{JQDataTablesPlugin}{DefaultConnector}
class additional css class to be added to the widget in addition to foswikiTable  
width width of table, e.g. 100% and the like  
web web to query for data current web
form data form definition  
paging, pager switch on/off paging data; NOTE: this parameter is deactivated when scrolling is specifed as well (see docu) off
pagelength, rows number of rows to be displayed when paging is enabled (see docu) 10
lengthmenu switches on a menu element to change the page length (see docu) [10, 25, 50, 100]
scrolling, scroller switch on/off dynamic scrolling; data will be fetched from the backend as you are scrolling up and down; NOTE: this parameter disables paging as these two features are mutual exclusive off
searching switch on/off the global search box (see docu) off
searchmode defines how to search, either using one search box covering all columns (global), or using one search box per column (multi) global
searchdelay delay before changes in a search box will cause new data to be fetched from the backend 400
sort specifies the column for initial ordering (see docu) first column
reverse specifies the initial ordering direction off
info switch on/off the info about the number of rows in the set (see docu) off
ordering switch on/off the ability to sort the table by clicking on a column header (see docu) on
scrollx switch on/off horizontal scrolling (see docu) off
scrolly specify vertical scrolling (see docu) off
scrollcollapse switch on/off collapsing of the table height on small data sets (see docu) off
columns comma-separated list of formfield names to be displayed; see the notes on special columns below all formfields of the DataForm specified by form
selecting switch on/off select extension  
selectproperty specifies the property of a row to be selected when selection is enabled topic
selectmode possible values are os, single, multi, this specifies the way a selection is made (see docu) multi
responsive switch on/off responsive extension  
fixedheader switch on/off fixed header extension  
<field-name>_title a column title for a given field-name, e.g. ProjectState_title="State" name of the formfield as specified in columns
<field-name>_width column width for a given field, e.g. ProjectID_width=5em  

Special column names

In general column names specified in the columns parameter of the %DATATABLE parameter directly relate to a formfield of an attached DataForm. Values are displayed and sorted according the the type of the formfield. There are however a few column names that have a special meaning or trigger an additional behavior on data in this column:

  • index: this is an auto-generated column enumerating the rows in a table
  • Date, Changed, Modified, Created, info.date, createdate: these fields are all date fields and treated accordingly
  • Topic: alias for topic
  • TopicTitle: displays the TopicTitle of a topic linking to it
  • By, Author: alias for author of recent topic revision
  • Creator: author if the initial topic revision
  • Worflow: name of the workflow assigned to a topic
  • A column name starting with a / (slash) will be excluded from any special treatment, i.e. /Author will not be interpreted as the author of the recent topic revision, but as the Author formfield of a DataForm (see below example).

Example

Below example lists all topics in the System web that have the PackageForm attached to it:

%STARTSECTION{"example3"}%
%DATATABLE{
   web="%SYSTEMWEB%"
   form="%SYSTEMWEB%.PackageForm"
   paging="on"
   searching="on"
   info="on"
   pagelength="10"
   lengthmenu="5, 10, 20, 50, 100"
   columns="index, Topic, Description, Version, Release, /Author"
}%
%ENDSECTION{"example3"}%

index Topic Description Version Release /Author

HTML5

A data table can also be applied to an already existing table enhancing it with additional features such as paging, client-side sorting, searching etc. This is done by wrapping your tables into a .jqDataTablesContainer div element and specify additional parameters using HTML5 data attributes

%JQREQUIRE{"datatables"}%
<div class="jqDataTablesContainer" data-paginate="true" data-searching="true" data-info="true">
| *Header* | *Header* | *Header* | *Header* |
| Data | Data | Data | Data |
| Data | Data | Data | Data |
| Data | Data | Data | Data |
| Data | Data | Data | Data |
</div>

See http://datatables.net/reference/index for a full list of all options.

<div class="jqDataTablesContainer" data-paginate="true" data-searching="true" data-info="true">
| *Header* | *Header* | *Header* | *Header* |
| Data | Data | Data | Data |
| Data | Data | Data | Data |
| Data | Data | Data | Data |
| Data | Data | Data | Data |
</div>

Example

This example generates a table dynamically using a FormattedSearch:

%STARTSECTION{"example1"}%
<div class="jqDataTablesContainer" data-paginate="true" data-searching="true" data-info="true">
%SEARCH{
  "1"
  type="query"
  topic="*Plugin"
  header="| *Name* | *Date* | *Author* |"
  format="| $topic | $date | $wikiusername |"
  nonoise="on"
}%
</div>
%ENDSECTION{"example1"}%

Name Date Author
AutoTemplatePlugin 18 Oct 2015 - 21:20 UnknownUser
AutoViewTemplatePlugin 01 May 2016 - 13:35 UnknownUser
BookmakerPlugin 01 Aug 2012 - 02:21 UnknownUser
BreadCrumbsPlugin 26 Sep 2015 - 23:37 UnknownUser
CaptchaPlugin 26 Sep 2015 - 23:39 UnknownUser
ClassificationPlugin 17 Jul 2015 - 23:46 UnknownUser
CommentPlugin 01 May 2016 - 13:38 UnknownUser
CompareRevisionsAddonPlugin 01 Oct 2015 - 13:47 UnknownUser
ConfigurePlugin 01 May 2016 - 13:42 UnknownUser
DBCachePlugin 04 Aug 2016 - 12:09 ChrisKenward
EditRowPlugin 01 May 2016 - 13:50 UnknownUser
EmptyPlugin 01 Oct 2015 - 13:47 UnknownUser
ExplicitNumberingPlugin 26 Aug 2011 - 01:08 UnknownUser
FilterPlugin 04 Aug 2016 - 12:09 ChrisKenward
FlexFormPlugin 04 Aug 2016 - 12:31 ChrisKenward
FlexPaperPlugin 23 Apr 2014 - 20:32 UnknownUser
FlexWebListPlugin 26 Sep 2015 - 01:08 UnknownUser
FootNotePlugin 13 Dec 2016 - 01:57 UnknownUser
GraphvizPlugin 02 Mar 2016 - 19:59 UnknownUser
GridLayoutPlugin 04 Aug 2016 - 12:31 ChrisKenward
HistoryPlugin 01 Oct 2015 - 13:47 UnknownUser
HomePagePlugin 01 Oct 2015 - 13:47 UnknownUser
ImageGalleryPlugin 04 Aug 2016 - 12:31 ChrisKenward
ImagePlugin 04 Aug 2016 - 12:31 ChrisKenward
InterwikiPlugin 01 May 2016 - 13:58 UnknownUser
JQDataTablesPlugin 13 Jun 2016 - 19:11 UnknownUser
JQGridPlugin 21 Sep 2015 - 22:50 UnknownUser
JQueryPlugin 01 May 2016 - 14:02 UnknownUser
LikePlugin 15 Oct 2015 - 17:40 UnknownUser
ListyPlugin 22 Sep 2015 - 21:38 UnknownUser
MailerContribPlugin 01 Oct 2015 - 13:47 UnknownUser
MediaElementPlugin 16 Dec 2014 - 00:50 UnknownUser
MetaCommentPlugin 29 Sep 2015 - 23:00 UnknownUser
MetaDataPlugin 17 Dec 2014 - 01:07 UnknownUser
MimeIconPlugin 18 Jul 2015 - 03:46 UnknownUser
MoreFormfieldsPlugin 01 Jun 2016 - 00:36 UnknownUser
MultiLingualPlugin 04 Aug 2016 - 12:49 ChrisKenward
NatEditPlugin 04 Aug 2016 - 12:58 ChrisKenward
NatSkinPlugin 04 Aug 2016 - 12:58 ChrisKenward
PageOptimizerPlugin 04 Aug 2016 - 12:38 ChrisKenward
PreferencesPlugin 01 Oct 2015 - 13:47 UnknownUser
PubLinkFixupPlugin 01 Oct 2015 - 13:47 UnknownUser
PublishPlugin 01 Aug 2012 - 03:00 UnknownUser
RedDotPlugin 18 Jul 2015 - 03:51 UnknownUser
RenderListPlugin 01 May 2016 - 23:15 UnknownUser
RenderPlugin 04 Aug 2016 - 12:59 ChrisKenward
SecurityHeadersPlugin 09 Mar 2016 - 09:26 UnknownUser
SlideShowPlugin 01 May 2016 - 23:18 UnknownUser
SmiliesPlugin 01 Oct 2015 - 13:47 UnknownUser
SolrPlugin 18 Oct 2015 - 21:07 UnknownUser
SpreadSheetPlugin 01 May 2016 - 23:25 UnknownUser
SubscribePlugin 04 Aug 2016 - 12:49 ChrisKenward
TWikiCompatibilityPlugin 01 Oct 2015 - 13:47 UnknownUser
TablePlugin 04 Feb 2016 - 05:34 UnknownUser
TinyMCEPlugin 01 Oct 2015 - 13:47 UnknownUser
TocPlugin 16 Apr 2011 - 08:05 UnknownUser
TopicInteractionPlugin 18 Jul 2015 - 03:35 UnknownUser
TwistyPlugin 01 May 2016 - 23:37 UnknownUser
UpdatesPlugin 01 Oct 2015 - 13:47 UnknownUser
WebLinkPlugin 30 Aug 2014 - 02:18 UnknownUser
WorkflowPlugin 11 Mar 2015 - 20:11 UnknownUser
WysiwygPlugin 01 May 2016 - 23:41 UnknownUser
ZonePlugin 06 Oct 2010 - 02:15 UnknownUser

JQDataTablesPlugin comes with additional sorting features for specific types of data:

  • numeric
  • string
  • date (extended to be able to parse Foswiki's default date format)
  • currency
  • metrics (e.g. killo, mega, giga, tera, ...)

Click on the table headers to sort the columns according to their data type.

%STARTSECTION{"example2"}%
<div class="jqDataTablesContainer">
| *#* | *String* | *Date* | *Number* | *Currency* | *Size* |
| 3 | ActionTrackerPlugin | 27 Jan 2010 - 17:07 | 1 | 1,00 | 10KB |
| 1 | AntiWikiSpamPlugin | 03 Jan 2013 - 09:07 | 10 | 10,00 | 3GB |
| 2 | RenderListPlugin | 13 May 2012 - 02:59 | 0.01 | 1,01 | 100MB |
| 5 | CommentPlugin | 10 Apr 2011 - 23:39 | 100 | 0,10 | 2024kB |
| 4 | FindElsewherePlugin | 23 Dec 2012 - 17:06 | 20 | 100,- | 0.1kB |
| 6 | JsonRpcContrib |  | 0 | -100,- | 1024TB |
</div>
%ENDSECTION{"example2"}%

# String Date Number Currency Size
3 ActionTrackerPlugin 27 Jan 2010 - 17:07 1 1,00 10KB
1 AntiWikiSpamPlugin 03 Jan 2013 - 09:07 10 10,00 3GB
2 RenderListPlugin 13 May 2012 - 02:59 0.01 1,01 100MB
5 CommentPlugin 10 Apr 2011 - 23:39 100 0,10 2024kB
4 FindElsewherePlugin 23 Dec 2012 - 17:06 20 100,- 0.1kB
6 JsonRpcContrib   0 -100,- 1024TB

Installation

You do not need to install anything in the browser to use this extension. The following instructions are for the administrator who installs the extension on the server.

Open configure, and open the "Extensions" section. Use "Find More Extensions" to get a list of available extensions. Select "Install".

If you have any problems, or if the extension isn't available in configure, then you can still install manually from the command-line. See http://foswiki.org/Support/ManuallyInstallingExtensions for more help.

Dependencies

NameVersionDescription
Foswiki::Plugins::JQueryPlugin >= 4.10Required

Change History

13 Jun 2016: fixed parsing of dates that are already epoch seconds; improved default settings of table layout
25 May 2016: updated to latest version of DataTables
22 Apr 2016: implemented server-side grid widget
18 Mar 2014: remove console.log() leftover; improve sorting date columns
09 Nov 2013: implemented sorting for currency, and metrics
08 Nov 2013: make it work under {NoConflict}; enable jquery-ui theming by default now; created a non-goofy default look and feel to play nicely with a skin's table design; only add DataTables support to specific tables, not all; make it configurable with declarative metadata; compress and minify plugin assets; remove files not required by the plugin; clean up controls and css classes added by TablePlugin's; added type detector for foswiki date columns
18 Jan 2013: Initial version

This site is powered by FoswikiCopyright © by the contributing authors. All material on this site is the property of the contributing authors.
Ideas, requests, problems regarding AustLII Communities? Send feedback