复杂表排序 [英] Complex Table Sorting
问题描述
我们在单个数据库中跟踪项目进度.在过去的两年中，管理要求改变了我们存储日期的格式(最初只是年份，然后是年份月份，现在是完整日期).为了确保数据库中没有任何投诉，以前的管理员将表设置为将日期存储在varchar中，而不是在date中.我已经创建了一个Web界面供用户访问和生成报告，但是该网页是静态的(用户端).管理层希望能够通过单击表标题进行排序.这很容易.我的问题是这些日期！由于格式，它们无法正确排序.
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.
以下是日期可以从PHP出现的三种形式:%Y，%Y%m和%c/%e/%Y.
Here's the three forms the dates can appear in from the PHP: %Y, %Y%m, and %c/%e/%Y.
我正在使用 tablesorter 对表格进行排序，但是我不知道如何通过jQuery.初始排序完全在MySQL查询中完成:
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:
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排序中复制日期列的排序.
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. ^^
解决方案:
我修改了查询，而不是按照最初的条件进行排序，而是将其保留为默认排序，并根据Mottie的建议创建了其他列以填充data-text属性.
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.
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生成的表的相应列中.最后，我设置了默认排序，并通过将以下内容添加到<HEAD>来指示它从data-text属性中提取(如果已设置):
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]]
});
谢谢您，莫蒂(Mottie)的快速答复和解决方案. :)
Thank you, Mottie, for the quick reply and solution. :)
推荐答案
也许最简单的解决方案是向每个包含格式正确的"日期字符串的表单元格添加数据属性
Maybe the easiest solution would be to add a data-attribute to each table cell which contains a "properly" formatted date string
<td data-text="1/1/2015">2015 01</td>
然后，如果您使用我的表分类器的叉子，则可以使用以下代码( 演示):
then, if you use my fork of tablesorter, you can use the following code (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
});
});
如果必须使用原始的表排序器，则可以将此代码与相同的HTML一起使用( demo ):
If you must use the original tablesorter, then you can use this code with the same HTML (demo):
$(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();
}
});
});
这篇关于复杂表排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
