Server-side Processing Custom Sort Solution (like formatted-num sorting plug-in)

callahan09

Hi, I was hoping I could get some help utilizing a custom sort plug-in...

I am trying to use the formatted-num plug-in. I have both the 'asc' and 'desc' functions in my .js files, and I've assigned the correct sType to my column, but it never sorts any differently than the default sort.

I placed a break in both formatted-num functions in my javascript debugger, and it seems that neither function is ever reached!

Please take a look at my datatables debug to see what's going on:

http://debug.datatables.net/epoceg

My table can be viewed at http://collection.callahan09.com/comics

I hope we can get this figured out... thanks in advance!

callahan09

Hello again,

After studying the dataTables script for a while, I saw some boolean checks in the sort function that basically say not to sort if we're doing server-side processing. So I then decided to change "bServerSide:" from true to false. Suddenly, my sort plug-in works perfectly. The problem is, the server-side processing is pretty important to me because the initial load time of the table is significantly worse when server-side is not enabled.

So how can I utilize these basic sort plug-ins AND have server-side processing enabled?

callahan09

I have perused the forums more. I tried searching before for info on these sorting plug-ins and server-side processing and came up nil, somehow. This time my search was more fruitful. Don't know how I missed it before. I've discovered the answer is that all sorting is done via the calls to the SQL server. Short answer: I can't use the sorting plug-ins. That's a real shame. I'm not nearly knowledgeable enough to figure out how to make SQL server return my results in the kind of sorted order I want it to. But it seems that's where I must go next, so that's what I'm undertaking now. Hopefully I have some good fortune in researching this and can learn how to do it.

Sorry to bother the forum with questions I wind up answering myself :)

allan

> Short answer: I can't use the sorting plug-ins.

Yup - the data is on the server, so client-side plug-ins aren't going to be much help :-). Server-side processing means what it says not he tin - all the data processing is one on the server.

Typically speaking the SQL server will do sorting based on the field format - timestamps will be sorted chronologically for example. Altering the sort in the SQL engine might require a plug-in for it (it will depend upon the server and exactly what you want to do).

Allan

callahan09

Thanks for the confirmation and information Allan, I appreciate it.

For anybody who stumbles here, I'm going to post my solution (I managed to get it to work, It wasn't too complex, after all!)...

First, you need to create a function on your database that can be used to strip out the non-numeric characters. I did so by creating two functions, actually (the one we'll be using in our query on the table relies on the other one). Here's the query I ran on my MySQL database to create the necessary functions:

[code]
CREATE FUNCTION IsNumeric (val varchar(255)) RETURNS tinyint
RETURN val REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

CREATE FUNCTION NumericOnly (val VARCHAR(255))
RETURNS INT
BEGIN
DECLARE idx INT DEFAULT 0;
IF ISNULL(val) THEN RETURN NULL; END IF;

IF LENGTH(val) = 0 THEN RETURN ""; END IF;

SET idx = LENGTH(val);
WHILE idx > 0 DO
IF IsNumeric(SUBSTRING(val,idx,1)) = 0 THEN
SET val = REPLACE(val,SUBSTRING(val,idx,1),"");
SET idx = LENGTH(val)+1;
END IF;
SET idx = idx - 1;
END WHILE;
RETURN CAST(val AS SIGNED);
END;
[/code]

Now you can just use that whenever you want to do a sort on a column that requires it. So instead of doing something like:

[code]
SELECT column FROM table ORDER BY column
[/code]

You would instead do:

[code]
SELECT column FROM table ORDER BY NumericOnly(column)
[/code]

But now we have to implement the use of this function into the server-side processing script we're using for DataTables for the columns we want to be formatted before sorting. For reference, I'm modifying the PHP 5.2 w/ MySQL script provided here: http://datatables.net/development/server-side/php_mysql.

Step 1 is to define an array with the column names you want to apply the special sorting formatting on. I did so just under the $aColumns definition at the beginning of the code. I gave it a very specific name indicating that it was a list of columns to be sorted by a specific type:

[code]
$aColumns_SortType_FormattedNum = array( 'ColumnName','OtherColumn' );
[/code]

Now, we need to change the Ordering segment of the script to the following:

[code]
/*
* Ordering
*/
$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i

allan

Nice one - thanks for sharing your solution with us!

Allan

callahan09

I've got my date sorting working now, too!

All of my date column values are either empty strings (date not available), a year by itself, the month name and the year, or the month name, day integer, and year. Examples of dates that appear:

1987
August 1992
September 3, 1999

That's really it. That corresponds to MySQL date formatting %M for month, %e for day, and %Y for year. There are many more options if you need to modify my function to suit your own needs. See here: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Now, my function:
[code]
CREATE FUNCTION `FormattedDate`(val varchar(255)) RETURNS date
BEGIN
DECLARE space_index INT default 0;
DECLARE comma_index INT default 0;
DECLARE date DATE default null;

IF ISNULL(val) THEN RETURN NULL; END IF;

IF LENGTH(val) = 0 THEN RETURN ""; END IF;

SET space_index = INSTR(val, ' ');
SET comma_index = INSTR(val, ',');
IF space_index = 0 THEN
SET date = STR_TO_DATE(val,'%Y');
ELSE
IF comma_index = 0 THEN
SET date = STR_TO_DATE(val,'%M %Y');
ELSE
SET date = STR_TO_DATE(val, '%M %e, %Y');
END IF;
END IF;

RETURN date;
END
[/code]

Pretty simple, really. But it needed a crude way of looking at the value and determining whether it was an empty string, a year alone, month year, or all three (month day, year) and then formatting the value accordingly.

So then it's just a matter of modifying the .php script to utilize this in the same manner I did with my FormattedNum sorting method.

I've got some columns using FormattedNum, some using FormattedDate, and some just being sorted on the default return value, so this is what my Ordering segment looks like now:

[code]
/*
* Ordering
*/
$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i 0)
{
$sort_direction = "asc";
}

$sOrder .= $open_modifier.$aColumns[ intval( $_GET['iSortCol_'.$i] ) ].$close_modifier."
".mysql_real_escape_string( $sort_direction ) .", ";
}
}

$sOrder = substr_replace( $sOrder, "", -2 );
if ( $sOrder == "ORDER BY" )
{
$sOrder = "";
}
}
[/code]

And of course I need to have a couple of arrays at the beginning to define which columns get special sorting treatment, like this:

[code]
$aColumns = array( 'NormalColumn','NumColumn','DateColumn' );
$aColumns_SortType_FormattedNum = array( 'NumColumn' );
$aColumns_SortType_FormattedDate = array( 'DateColumn' );
[/code]

I hope this finds a use for someone else!

rw1

hello,

would anyone be able to help a relative php/datatables newb implement this.

all i want to do is sort the data that is in the third column by date (in the format 24/01/10).

my efforts thus far are here:

http://datatables.net/forums/discussion/11904/how-do-i-make-a-column-sortable-by-ddmmyy-with-server-side-processing

thank you.

allan

> all i want to do is sort the data that is in the third column by date (in the format 24/01/10).

What is the data type of the column? If it is anything other than a date / time type then I'd very strongly suggest you change it. The date type should relate to what is being held int he field - then the SQL engine can apply its own sorting, filtering etc extremely efficiently.

Allan