Getting value of hidden column when clicking on row (& SQL 2005 tip!)
Getting value of hidden column when clicking on row (& SQL 2005 tip!)
Firstly, this is a brilliant jQuery plugin and the recent server-side functionality is helping me page through 1000's of records. I have a great tip for anyone using SQL Server 2005/8, but first my questions.
I would like to make each row clickable which would take the user to a different page where they can edit the record in detail. For this to work I need the record ID which is returned in the first column I get back through the JSON request. I've hidden this column because users don't need to see it. Please can someone show me an example of how to make every paged row clickable with a url param based on data from the first column?
Allan, I would definitely be in support of a built-in argument to increase the delay on the filter function. At the moment when someone types in the filter box it hammers our server with requests at every keystroke which is extremely inefficient and adds unnecessary load to our server. With a couple of users at once typing in it looks like a DoS attack on the server! ;-) A separate "Search" button or a delay of 1 sec would make a massive difference.
Now my tip for SQL 2005/8 users. A new function called row_number() was added that gives each row its own incremental number based on whatever ORDER BY sorting you want. This means you can sort by any column (or a more complex sort function) and only get your database to return the rows required to populate the next page. So if you have 1000's of records you shouldn't be asking the database to return all of them if you only want to display the next 25 records. Techniques exist to do this but have been cumbersome or slow. Here's a simple query showing how to return records 150 to 175 only using this 2005/8 function:
SELECT * FROM (
SELECT row_number() over (order by users.surname, users.firstname) AS rowNumber,
users.id, users.firstname, users.surname
FROM users
) table1
WHERE table1.rowNumber BETWEEN 150 AND 175
Hope that helps someone - I was Googling for a while to perfect this.
I would like to make each row clickable which would take the user to a different page where they can edit the record in detail. For this to work I need the record ID which is returned in the first column I get back through the JSON request. I've hidden this column because users don't need to see it. Please can someone show me an example of how to make every paged row clickable with a url param based on data from the first column?
Allan, I would definitely be in support of a built-in argument to increase the delay on the filter function. At the moment when someone types in the filter box it hammers our server with requests at every keystroke which is extremely inefficient and adds unnecessary load to our server. With a couple of users at once typing in it looks like a DoS attack on the server! ;-) A separate "Search" button or a delay of 1 sec would make a massive difference.
Now my tip for SQL 2005/8 users. A new function called row_number() was added that gives each row its own incremental number based on whatever ORDER BY sorting you want. This means you can sort by any column (or a more complex sort function) and only get your database to return the rows required to populate the next page. So if you have 1000's of records you shouldn't be asking the database to return all of them if you only want to display the next 25 records. Techniques exist to do this but have been cumbersome or slow. Here's a simple query showing how to return records 150 to 175 only using this 2005/8 function:
SELECT * FROM (
SELECT row_number() over (order by users.surname, users.firstname) AS rowNumber,
users.id, users.firstname, users.surname
FROM users
) table1
WHERE table1.rowNumber BETWEEN 150 AND 175
Hope that helps someone - I was Googling for a while to perfect this.
This discussion has been closed.
Replies
Thanks for the compliments! Much appreciated!
1. Clickable rows: Since you are using server-side processing here, what to do is use fnDrawCallback ( http://datatables.net/usage#fnDrawCallback ) to add the click event listeners. Try something like this:
[code]
var oTable;
$(document).ready( function () {
oTable = $('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "../examples_support/server_processing.php",
"aoColumns": [
{ "bVisible": false },
null,
null,
null,
null
],
"fnDrawCallback": function ( oSettings ) {
$('#example tbody tr').each( function () {
var iPos = oTable.fnGetPosition( this );
$(this).click( function () {
window.location = "whatever.php?id="+oSettings.aoData[iPos]._aData[0];
} );
} );
}
} );
} );
[/code]
2. Delay in filtering: This can be achieved using the API function fnSetFilteringDelay() :-)
http://datatables.net/plug-ins#api_fnSetFilteringDelay
3. SQL 2005: Thanks for the tip - hopefully some one will find it useful. Does SQL 2005 not support the LIMIT keyword? In my examples I've used LIMIT to constrain the returned number of results ( http://datatables.net/1.5-beta/examples/data_sources/server_side.html ) - but this is MySQL. I've not used SQL 2005, so am not sure of it's capabilities.
Regards,
Allan
Thanks very much for the code, I'll try it out in a minute. I saw the plugin for fnSetFilteringDelay() but really wanted to know if it can be done with just 1 or 2 lines of code as opposed to a separate plugin? (I'm concious of limited file requests and the amount of code used, perhaps analy so!!!).
MS SQL Server has TOP instead of LIMIT but is only able to get the first x number of rows, there is no 2nd parameter unlike MySQL's LIMIT. Of course when you are paging you don't want to first x rows for any page beyond the very first one so TOP is not very helpful which I guess is why row_number() was added to the official SQL 2003 language specification.
I'll sort out a donation via paypal for using your plugin. Your support alone is well worth anyone donating some money, especially if it saves them a few hours of head scratching. Thank you.
sData is undefined
https://blah... /jquery.dataTables.js
Line 447
for ( i=0 ; i
Regarding the fnSetFilteringDelay() function - this is most odd - this error normally crops up when the headers don't match the columns, or there is some missing data somewhere. Is the only change you made to your code to include fnSetFilteringDelay()?
About using this as a separate file - you could just concatenate your various JS files together (an asp or php script could even be made to do this on the fly). I completely agree about trying to minimise http requests - and I tend to use a 'deploy' script on most of my sites to concat the js files together. And Conditional-CSS does it for my CSS :-)
Finally (for now) - thanks very much for the donation :-)
Regards,
Allan
Can you post a link to an example page which shows the problem? That would certainly help debugging. Otherwise, could you perhaps detail a little bit more about your initialisation code?
Here is the code that works for me:
[code]
jQuery.fn.dataTableExt.oApi.fnSetFilteringDelay = function ( oSettings, iDelay ) {
iDelay = (iDelay && (/^[0-9]+$/.test(iDelay))) ? iDelay : 250;
var $this = this, oTimerId;
// Unfortunately there is no nFilter inside oSettings.
var anControl = $( 'div.dataTables_filter input:text' );
anControl.unbind( 'keyup' ).bind( 'keyup', function() {
var $$this = $this;
window.clearTimeout(oTimerId);
oTimerId = window.setTimeout(function() {
$$this.fnFilter( anControl.val() );
}, iDelay);
});
return this;
}
$(document).ready(function() {
var oTable = $('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "../examples_support/server_processing.php"
} );
oTable.fnSetFilteringDelay(250);
} );
[/code]
Allan
The comments in the plugin say to call the function like this:
$('#example').dataTable().fnSetFilteringDelay(1000);
But when I copied your example, like this:
oTable.fnSetFilteringDelay(250);
it works! Since my table is called #example I didn't see a problem with the previous way to call the function.
Thanks Allan!
Gary.
Hmm - that's odd - chaining the plug-in function like the example certainly should work - I'll update the example code to this more reliable method... It could be a funny interaction with the server-side processing...
Allan