CODEWITHSUNDEEP
jquerytablesorter
Ewajs
Ewajs

Reputation: 31

Change TableSorter sorting criteria

I'm trying to use the jQuery TableSorter plugin, to sort a table of books that I create by querying a MySQL database with PHP.

The table is initially sorted by title by the MySQL engine (ORDER BY Book.Title in the query) and then the user is free to sort the table in a different manner via TableSorter (number of pages, author name, publication date, &c.).

The thing is, some books have names that start with numbers (such as "1984" or "20.000 Leagues Under the Sea"). MySQL puts these books on the top, before any other books that start with a letter. I have no quarrel with this, but a strange "bug" (not really a bug maybe) emerges with TableSorter: it only sorts the number-starting titles (shifting them from the beggining of the table to the end), leaving the rest (and vast majority) of items with their order unchanged. I think I've tracked down the origin of the problem, but couldn't yet find a (true) solution:

It seems that TableSorter looks only at the first row to determine the type of the column and deems it as a numerical one because the first element is a book whose title starts with a number, it then ignores any cell that starts with an alphabetic character. If I reverse the order of the original sorting (changing the MySQL query ending to ORDER BY Book.Title DESC), then the first element is a book starting with a letter (Z or the nearest one), TableSorter thinks it's an alphanumerical column and sorts it just fine, but then the table is presented in an unusual and awkward ordering to the user.

For those who have the same problem (I don't think I'm the first one to run into this, but I couldn't find any posts relating to this "bug", nor a way to fix it), I managed to devise a quick fix: putting a hidden row first that forces each column sorting criteria to the right one, like this:

  <tr>
      <td class = "row-hidden">ABC</td> <!-- Title -->
      <td class = "row-hidden">ABC</td> <!-- Subtitle -->
      <td class = "row-hidden">ABC</td> <!-- Author -->
      <td class = "row-hidden">123</td> <!-- Pages -->
      <td class = "row-hidden">123</td> <!-- Quotes -->
      <td class = "row-hidden">01/01/1970</td> <!-- Publication Date -->
      <td class = "row-hidden"></td> <!-- No Sorting Required -->
  </tr>

But this is just a bypass and I'd like to know if there's a way to solve the actual problem, basically the question is this: Is there a way to force/set the sorting criteria of any given column (via jQuery) manually thus overriding what the parser decides automatically??

Upvotes: 1

Views: 559

Answers (1)

Mottie
Mottie

Reputation: 86403

Tablesorter attempts to determine the type of data contained in a column by starting from the first row and scanning down until one of the parsers hits a match. It shouldn't, but when it sees "20.000 Leagues..." it thinks it's looking at numeric only data, so the parser gets set to "digit".

To override this behavior, set a headers option for that column. Once set, the column data is no longer auto-detected.

$('table').tablesorter({
  headers : {
    // using a zero-based column index
    0 : { sorter: 'text' }
  }
});

In case you're interested, I also have a fork of tablesorter which includes, but still needs to be loaded, an ignore leading articles parser which properly sorts titles by ignoring leading articles "A", "An" and "The". Other languages are also supported.

Upvotes: 1

Related Questions