Changing behaviour of fnPageChange

dmg

Hi,

I'm using server side processing with a large MySQL table and I could do the pagination much more efficiently if, when making a server side request I can

(i) indicate whether fnPageChange was called and, if so, whether it received "next" or "prev" as a parameter, and;

(ii) If fnPageChange was supplied "next" as a parameter, send the data from the last row or if fnPageChange was supplied "first", send the data from the first row of the table's current state.

Part (ii) seems straightforward, I'm doing it using fnServerData to push the values from the last and first rows into the AJAX data array.

However, it appears by the time fnServerData is called, iDisplayStart has already been incremented (decremented) when paginating to the next (previous) page. So part (i) looks tricky. I could keep track of things on the server side, but it would be messy.

All I really need is a single variable included in the data sent to the server that will be set to indicate what (if any) parameter was supplied to fnPageChange. I could probably just edit this myself in the source code, but I don't know Javascript very well and it seems like a recipe for disaster down the road when I've forgotten all about this and upgrade.

Thanks.

dmg

bump

allan

Hi dmg,

There are a couple of options for this I think, but possibly the cleanest without changing the DataTables core is to make use of fnServerParams and pass in a bit of data which will indicate if fnPageChange was clicked. Something like:

[code]
var pageChange = null;

$('#click_next').click( function () {
pageChange = "next";
oTable.fnPageChange(pageChange);
} );

$('#table').dataTable( {
"fnServerParams": function ( aoData ) {
aoData.push( { name: "pageChange", value: pageChange } );
pageChange = null; // Reset so that sorting etc doesn't cause a page change
}
} );
[/code]

Out of interest, how will this make things faster? Are you caching the MySQL result without a limit?

Allan

dmg

Excellent, thanks! :)

The reason this will make things faster is due to the way MySQL uses OFFSET in a SELECT query.

Basically, when doing a select query like

[code]
SELECT ...
FROM ...
WHERE ...
ORDER BY ...
LIMIT offset, length
[/code]

MySQL needs to first get offset + length records before it can apply the WHERE criteria or do any sorting (more on that, plus a workaround (that I can't use due to my table structure) here: http://stackoverflow.com/questions/4481388/why-does-mysql-higher-limit-offset-slow-the-query-down).

This won't matter if the table isn't too big. But mine has several million rows and I have a lot of advanced search type restrictions the user can add that complicate the SQL query, so things can get slow.

The way this helps me is as follows:

Say my table has a primary key "col_0".

Upon the user clicking 'next' I'll note what the value (call it "val_0") of col_0 was in the last row of the datatable prior to 'next' being clicked.

Then, I'll just include "col_0 > val_0" in my WHERE criteria and leave OFFSET at 0.

(Similarly when they click previous, but using the value of col_0 in the first row instead and replace ">" with "<").

In short, by telling MySQL where you were before the pagination, MySQL can immediately discard all the rows it would have otherwise had to iterate through until it reached the offset (i.e., iDisplayStart).

allan

Awesome - thanks for explaining that! SQL engines sure do have a lot of configurability :-).

Regards,
Allan