复杂表格排序(Complex Table Sorting)

我们在单个数据库中跟踪项目进度。在过去两年中，管理层的要求改变了我们存储日期的格式（最初只是年份，然后是年月，现在是完整日期）。为确保没有来自数据库的投诉，以前的管理员设置表以将日期存储在varchar而不是date 。我已经创建了一个Web界面供用户访问和生成报告，但网页是静态的（用户端）。管理层希望能够通过单击表标题进行排序。这很容易。我的问题是那些日期！由于格式，它们没有正确排序。

以下是日期可以从PHP中显示的三种形式： %Y ， %Y%m和%c/%e/%Y 。

我正在使用tablesorter对表进行排序，但我无法弄清楚如何通过jQuery对日期进行排序。初始排序完全在MySQL查询中完成：

ORDER BY CASE WHEN cstatus = 'Complete' THEN 1 ELSE 0 END DESC, CASE WHEN CHAR_LENGTH(cdevelopmentStart) > 6 THEN DATE_FORMAT(STR_TO_DATE(cdevelopmentStart, '%m/%d/%Y'), '%c/%e/%2014') ELSE CASE WHEN CHAR_LENGTH(cdevelopmentStart) > 4 THEN DATE_FORMAT(STR_TO_DATE(cdevelopmentStart, '%Y%m'), '%c/%e/%Y') ELSE DATE_FORMAT(STR_TO_DATE(cdevelopmentStart, '%Y'), '%c/%e/%Y') END END DESC, id DESC

我需要在jQuery排序中复制日期列的排序。

希望我晚上休息后会有更多的运气，但我想发布这个以防万一有人能给我一个开头。 ^^

解：

我将查询修改为，而不是按条件排序，最初将其保留为默认排序并创建其他列以填充data-text属性，如Mottie所建议的那样。

SELECT CASE WHEN cdevelopmentStart IS NULL THEN '01/01/2012' ELSE CASE WHEN CHAR_LENGTH(cdevelopmentStart) > 6 THEN DATE_FORMAT(STR_TO_DATE(cdevelopmentStart, '%m/%d/%Y'), '%m/%d/%Y') ELSE CASE WHEN CHAR_LENGTH(cdevelopmentStart) > 4 THEN DATE_FORMAT(STR_TO_DATE(cdevelopmentStart, '%Y%m'), '%m/01/%Y') ELSE DATE_FORMAT(STR_TO_DATE(cdevelopmentStart, '%Y'), '01/01/%Y') END END END AS developmentSort, CASE WHEN courses.cstatus = 'Complete' THEN 0 ELSE CASE WHEN courses.cstatus = 'Cancelled' THEN 1 ELSE CASE WHEN courses.cstatus = 'Hold' THEN 2 ELSE CASE WHEN courses.cstatus = 'Review' THEN 3 ELSE CASE WHEN courses.cstatus = 'Rework' THEN 4 ELSE CASE WHEN courses.cstatus = 'Open' THEN 5 ELSE 6 END END END END END END AS statusSort

然后，我将data-text="' . $result['developmentSort'] . '"和data-text="' . $result['statusSort'] . '"到PHP生成的表的相应列中。最后，我设置了默认排序并指示它从data-text属性中提取（如果已设置），方法是将以下内容添加到<HEAD> ：

$("#mainlist").tablesorter({ textAttribute: 'data-text', usNumberFormat: true, sortList: [[19, 0], [6, 1], [0, 1]] });

感谢Mottie的快速回复和解决方案。 :)

We track project progression in a single database. Over the last two years, the requirements of management have changed the format of which we store dates (originally, just the year, then year month, now full dates). To ensure there were no complaints from the database, the previous administrator set-up the table to store the dates in varchar instead of date. I've created a web-interface for users to access and generate reports, but the webpage is static (user-side). Management wants to be able to sort by click on the table headers. That's easy. My problem is those dates! Because of the formats, they don't sort correctly.

Here's the three forms the dates can appear in from the PHP: %Y, %Y%m, and %c/%e/%Y.

I'm using tablesorter to sort the table, but I can't figure out how to sort the dates via the jQuery. The inital sorting is done entirely in the MySQL query:

I need to replicate the sorting for the date columns in the jQuery sorting.

Hopefully, I'll have more luck after a nights rest, but I wanted to post this up in case anyone can give me a headstart. ^^

Solution:

I modified the query to, instead of sorting by the conditions initially, leave it to default sorting and creating additional columns to populate the data-text attribute, as suggested by Mottie.

Then, I added data-text="' . $result['developmentSort'] . '" and data-text="' . $result['statusSort'] . '" to the corresponding columns of the PHP-generated table. Lastly, I set-up the default sorting and instructed it to pull from the data-text attribute, if set, by adding the following to the <HEAD>:

$("#mainlist").tablesorter({ textAttribute: 'data-text', usNumberFormat: true, sortList: [[19, 0], [6, 1], [0, 1]] });

Thank you, Mottie, for the quick reply and solution. :)

最满意答案

也许最简单的解决方案是向每个包含“正确”格式化日期字符串的表格单元格添加数据属性

那么，如果你使用我的fork of tableorter ，你可以使用以下代码（ demo ）：

$(function () { $('table').tablesorter({ theme: 'blue', // data-attribute that contains alternate cell text textAttribute: 'data-text', // false for German "1.234.567,89" or French "1 234 567,89" usNumberFormat: true }); });

如果您必须使用原始tablesorter，那么您可以将此代码与相同的HTML（演示）一起使用：

$(function () { $('table').tablesorter({ // set to "us", "pt", "uk", "dd/mm/yy" dateFormat: "us", // custom table cell text extraction method textExtraction: function (node) { var $node = $(node); return $node.attr('data-text') || $node.html(); } }); });

Maybe the easiest solution would be to add a data-attribute to each table cell which contains a "properly" formatted date string

then, if you use my fork of tablesorter, you can use the following code (demo):

If you must use the original tablesorter, then you can use this code with the same HTML (demo):

更多推荐