Sorting data

chris geiger

Hi Guys,

I've tried using plugins on the site to properly sort data but it ends up breaking the datatable all together.

I want to sort by:

Column 1 - Number (0 lowest, 999999 highest)
Column 2 - Regular sorting, just names
Column 3 - Currency (with $)
Column 4 - Date ( MM/dd/YY [ Jan/4/2011 ] )
Column 5 - Date ( MM/dd/YY [ Jan/4/2011 ] )
Column 6 - Regular sorting, just names

how can I accomplish this? What am I doing wrong?

My code looks like this ( so far )

[code]
$(document).ready(function() {
$('.datatable').dataTable( {
"sPaginationType": "full_numbers",
"iDisplayLength": 25,
"aaSorting": [[ 0, "desc" ]],
"aoColumns": [
{ "sType": "num-html-desc" },
null,
{ "sType": "currency-desc" },
null,
null,
null
]
} );
} );
[/code]

chris geiger

The first number column also has a link wrapped around it.

allan

H Chris,

I think you are quite close to what you are looking for, but rather than specifying the sorting direction in sType (the "-desc" part) just drop that and let DataTables pick which direction the sorting should be in (i.e. it might get reversed by the user to be 'asc'). Something like this:

[code]
$(document).ready(function() {
$('.datatable').dataTable( {
"sPaginationType": "full_numbers",
"iDisplayLength": 25,
"aaSorting": [[ 0, "desc" ]],
"aoColumns": [
{ "sType": "num-html" },
null,
{ "sType": "currency" },
null,
null,
null
]
} );
} );
[/code]
Also for the date sorting, this is done by the Javascript built-in Date.parse() function. It will happily pick up the MM/dd/YY format, but not mmm/d/YYYY. For that you would need to use another sorting plug-in :-)

Regards,
Allan

chris geiger

Allan,

It sorts it fine on init but breaks the pagination. And it also doesmt allow for sorting ( when clicking columns ). How can I fix that?

allan

Hi Chris,

Can you link me to your working example please? When you say that it breaks the pagination, what aspect of it is broken? I suspect that you are getting a Javascript error (have a look at the Firebug console) - the code above looks fine, but have you remembered to include the sorting plug-ins you need?

Regards,
Allan

chris geiger

Allan,

What's happening with pagination is it's just showing me all of the data on one page. When I click on a column that doesnt have the pre-init sorting it adds the pagination.

The error the console is spitting out is:
[code]TypeError: Result of expression 'q[d+"-"+k[f][1]]' [undefined] is not a function.[/code]

Here is the plugins I have added:
[code]
jQuery.fn.dataTableExt.oSort['currency-asc'] = function(a,b) {
/* Remove any commas (assumes that if present all strings will have a fixed number of d.p) */
var x = a == "-" ? 0 : a.replace( /,/g, "" );
var y = b == "-" ? 0 : b.replace( /,/g, "" );

/* Remove the currency sign */
x = x.substring( 1 );
y = y.substring( 1 );

/* Parse and return */
x = parseFloat( x );
y = parseFloat( y );
return x - y;
};

jQuery.fn.dataTableExt.oSort['currency-desc'] = function(a,b) {
/* Remove any commas (assumes that if present all strings will have a fixed number of d.p) */
var x = a == "-" ? 0 : a.replace( /,/g, "" );
var y = b == "-" ? 0 : b.replace( /,/g, "" );

/* Remove the currency sign */
x = x.substring( 1 );
y = y.substring( 1 );

/* Parse and return */
x = parseFloat( x );
y = parseFloat( y );
return y - x;
};

jQuery.fn.dataTableExt.oSort['num-html-asc'] = function(a,b) {
var x = a.replace( /<.*?>/g, "" );
var y = b.replace( /<.*?>/g, "" );
x = parseFloat( x );
y = parseFloat( y );
return ((x < y) ? -1 : ((x > y) ? 1 : 0));
};

jQuery.fn.dataTableExt.oSort['num-html-desc'] = function(a,b) {
var x = a.replace( /<.*?>/g, "" );
var y = b.replace( /<.*?>/g, "" );
x = parseFloat( x );
y = parseFloat( y );
return ((x < y) ? 1 : ((x > y) ? -1 : 0));
};
[/code]

allan

Thanks for the console message - it looks like DataTables might not be seeing the plug-ins for some reason. Have you included them before you initialise the table, but after you include DataTables itself? Also, worth checking you haven't included DataTables twice by mistake.

Allan

chris geiger

Very strange Allan,

Everything is loaded in the correct order.

Jquery -> Datatables JS -> Plugins -> Init code

I double checked that datatables is not loaded twice.

I did load all of this data at the footer of the page instead of the header, to speed up page load times, is that going to affect it at all?

Chris.

allan

No that should be absolutely fine. Is there any chance you can give me a link so I can see what might be happening? http://datatables.net/contact if you don't want to make the URL public :-)

Allan

chris geiger

Allan,

URL sent through your contact form.

allan

Hi Chris,

Thanks very much for the link! In your code you've got:

[code]
$(document).ready(function() {
$('.datatable').dataTable( {
"sPaginationType": "full_numbers",
"iDisplayLength": 25,
"aaSorting": [[ 0, "desc" ]],
"aoColumns": [
{ "sType": "num-html-desc" },
null,
{ "sType": "currency-desc" },
null,
null,
null
]
} );
} );
[/code]
You need to remove the "-desc" from the two 'sType' definitions, as previously discussed. Hopefully that should do it :-)

Allan

chris geiger

Works great! What happens if I put a # before numbers, would it recognize it?

Also allan, is it possible to change the "No data in this table" message on a per table basis? Something maybe I can throw into the init code?

allan

You would need to add a small modification to the two plug-ins you are using if you want to prefix numbers with a hash in either of them. Likewise in other column, if you want to prefix a hash then it will need a different sorting plug-in (the currency one should be easy to modify for that).

Regarding the language configuration - yes indeed this is possible: http://datatables.net/usage/i18n#oLanguage.sEmptyTable and http://datatables.net/usage/i18n#oLanguage.sZeroRecords (each slightly different in operation). To do it on a per table basis, you'll need to split your initialisation of the DataTable into each table (i.e. $('#example_Table').dataTable({...}); ), rather than using the class selector you've got at the moment.

Allan