Item14579: Foswiki dates do not sort properly using dbcache connector

Formfields that contain foswiki dates (DD MMM YYYY - HH:MM) are not sorted properly when using the dbcache connector and DATATABLES macro. We noticed the issue using the RenderTopicsOfType DBCALL. It seems clear in the perl code that all dates should be in epoch format for storage, however, we need to store our dates in foswiki format.

DBQUERY calls do properly sort foswiki dates due to the javascript extending datatable sort, however, this only works when clicking the column header. The initial load order is not correct. Additionally, the alternating grey and white stripes do not update upon reorder, they retain their original colors.

It would be very helpful if the DATATABLES macro could properly sort foswiki dates since it offers numerous advantages over simple DBQUERY calls inside jqdatatable divs. There is an input called _formatter, which as far as I can tell has not been implemented. This could be one option to specify field formats for ordering.

Any suggestions on how to solve this issue would be very helpful.

-- KarlDuderstadt - 15 Dec 2017

Can you add more details, please, ie the data form definition and the %DATATABLE code in use?

-- MichaelDaum - 15 Dec 2017

We have created a backend for JQFullCalendarPlugin in which events rendered on the calendar are stored as topics with date-time formfields Start and End used for calendar rendering. We noticed the issue when simply making a table with all the events.

Here is a form definition as an example (topic TestEventEntry):

Name:	Type:	Size:	Values:	Description:	Attributes:	Default:
TopicType	label	1	TestEventEntry, ClassifiedTopic, CategorizedTopic, TaggedTopic	Document type	H
Color	text	75		Event Color	H
Author	text	75		Author of this event entry
TopicTitle	text	75		Event name
Tag	tag	30		keywords
Category	cat	1		Categories this event entry is filed under.
Start	date	20		Start date and time
End	date	20		End date and time

Typically we use RenderTopicsOfType DBCALL with dbcache connector

%DBCALL{"Applications.RenderTopicsOfType"
  SORT="Start"
  FIELDS="TopicTitle, Start, End"
}%

As you can see, even with sort set to Start, the dates are not in the correct order.

Here is just a DATATABLE macro example.

%DATATABLE{
   web="Applications.Scheduler"
   form="Applications.Scheduler.TestEventEntry"
   paging="on"
   sort="Start"
   searching="on"
   info="on"
   pagelength="10"
   lengthmenu="5, 10, 20, 50, 100"
   columns="TopicTitle, Start, End"
}%

Here as well, you can see the dates are not in the correct order.

Looking at the perl scripts (see below for relevant sections), it seems there are some special formfields that will be sorted as dates, but the expectation is that they are stored in epoch seconds. So even using these special names, any field that is stored in foswiki date format (DD MMM YYYY - HH:MM) will not sort properly.

Relavent sections of Perl script - JQDataTablesPlugin/lib/Foswiki/Plugins/JQDataTablesPlugin/DBCacheConnector.pm (lines 238-248) :

} elsif (!$isEscaped && $propertyName =~ /^(Date|Changed|Modified|Created|info\.date|createdate|publishdate)$/) {
        my $html =
          $cell
          ? "<span style='white-space:nowrap'>" . Foswiki::Time::formatTime($cell) . "</span>"
          : "";
        $cell = {
          "display" => $html,
          "epoch" => $cell || 0,
          "raw" => Foswiki::Time::formatTime($cell || 0),
        };
      }

Relavent sections of Perl script - JQDataTablesPlugin/lib/Foswiki/Plugins/JQDataTablesPlugin/DataTables.pm (lines 245-251) :

elsif ($fieldName =~ /^(Date|Changed|Modified|Created|info\.date|createdate)$/) {
      $col->{render} = {
        "_" => "raw",
        "display" => "display",
        "sort" => "epoch",
      };
    }

In contrast, here is an example with DBQUERY.

%JQREQUIRE{"datatables"}%
<div class="jqDataTablesContainer" data-paging="true" data-searching="true" data-info="false" data-ordering="true" data-scroll-x="false" data-scroll-collapse="false" data-search-delay="400" data-length-change="true" data-length-menu="[5,10,20,30,50,100]" data-page-length="50">
%DBQUERY{
  "TopicType~'\bTestEventEntry\b'"
  type="query" 
  sort="Start"
  web="Applications.Scheduler"
  header="| *Topic Title* | *Start* | *End* |$n"
  format="| $formfield(TopicTitle) | $formfield(Start) | $formfield(End) |"
}%
</div>

Here initially the dates are not sorted correctly, but when clicking the column header the javascript sort extension kicks in and they sort correctly:

In both cases the white and grey stripes are not correct. It would be great to have a way specify the format for sorting in the DATATABLES macro that would then work with the RenderTopicsOfType DBCALL. Is that the best solution or is there a mistake in my examples above?

-- KarlDuderstadt - 15 Dec 2017

Thank you for the very detailed information.

-- MichaelDaum - 18 Dec 2017

Next version will add JQMomentContrib to the soup allowing you to specify a date format using a data-date-time-format and data-date-time-locale= HTML5 parameters.

Also, next version of DBCachePlugin/Contrib will store date formfields in epoch seconds so that these sort properly right out of the box using %DATATABLE. See Item14704.

-- MichaelDaum - 11 Jun 2018

Commenting is disabled while not logged in