Item14579: Foswiki dates do not sort properly using dbcache connector
Priority: Enhancement
Current State: Closed
Released In: n/a
Target Release: n/a
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