Back to the index of articles and examples

DataTable Requery

This page shows my requery custom method for YUI DataTable:

The method has to be included in the page that will use it at any time after the source code for the DataTable gets loaded but before any DataTable is instantiated. The method accepts a single argument newRequest which can be:

If not provided, the requery will use the same values as set initially.

Below you will find several examples with local, remote (XHR) and function DataSources and with client-side or server-side sorting and/or paging. All use a slightly modified version of the PHP script (json_meta_proxy.php) that comes with the YUI examples, the modification only affects the last example as noted below.

Examples

Please read:

There are several difficult issues with requerying. The first three examples don't have sorting enabled, and there is a good reason for that. If the user sorts by whichever column and the data is requeried, it will not return sorted as the user requested, unless the server somehow helps in some way. With local data, there is no server involved, so even the sorting has to be handled on the client side. The other option is to capture the data before it is received by the DataTable (dataReturnEvent) and sort it there. This was not shown since, after all, not all DataTables need to be sorted.

In the Client-side paging example I do request the server to return the data sorted as the user has selected. Though the DataTable will be sorted and paged locally, on the requery, the sort information is sent to the server so the new batch of data comes sorted the right way.

In the server-side paging and sorting example, we use the server to the full, always doing the paging and sorting via the server.

Another issue is row and cell selection: the DataTable keeps track of selection via recordIds. These are reassigned to the new records on requerying the server. The requery function will preserve the selection as much as the DataTable is capable of, which simply doesn't work when the records come in a different order, a new record is inserted or an existing one is deleted, the recordIds will point to the wrong records. If you want to support this functionality, you have to find the means to identify the selected records with some immutable piece of data (a database primary key or such), do the requery, and re-establish the selection once the data has been received.

Finally, in doing the tests I discovered a bug in the DataTable which I duly reported, the patch for that bug is included in the source for this example, methods onDataReturnSetRows and _handleDataReturnPayload.

The important message is: requerying the database may be tricky in certain circumstances, you can't trust a simple method to do it all, it depends very much on your application, in many cases, you need support from the server.

Using Remote DataSource via XHR

The first example XHRDataSource was instantiated with the base URL of the server page providing the data and the extra URL arguments, stating the startIndex and number of records expected, was given in the initialRequest configuration attribute of the DataTable: initialRequest:'startIndex=0&results=12'

The requery takes a different string to be used as URL arguments which will be appended to the same base URL.

Using Local DataSource

The second, a LocalDataSource, was instantiated with a slice of a big array of data fetched separately via Connection Manager. On doing the requery, we provide a different slice of that same data.

Using Function DataSource

The third, a FunctionDataSource, was instantiated with the code of the function that will retrieve the data. The function simply returns a slice of the very same data that the previous DataTable uses. To tell the function which slice of the data we want, we provide an initialRequest configuration attribute to the DataTable with an object literal containing the start and end indexes. On doing the requery, we simply provide a different set of arguments, the function remains the same.

Client-side paging and sorting with XHR DataSource

For the next example, we use a a remote DataSource as in the first example, but we set a paginator for the DataTable. Since the server can provide a variable number of results, when requerying we ask for less records than the first time around to show how the requery will reset the paginator to show the correct number of pages. Try this: if you go to page 2 and requery it will show the new, shorted, second page. Now, reload the page, go to page 4 and do the requery, the paginator will now show the first page since there is no further page 4 in the new batch.

Though this is a client-side sorting example, when we do the requery, we have to ask the server to provide those records sorted in whichever way the user has selected. Somehow, it is a hybrid example in that the server does have to do some sorting to reflect what the user has done. However, further clicking on the column headers will do the sort locally on the client side.

Server-side paging and sorting with XHR DataSource

With server-side paging and sorting, evert change in page or sorting will create a new request but if you want to refresh the current page, you can't, the button for that page is not even active so the requery forces a request for that same page. We have added an extra trick, the server allows us to tell it how many total records I want reported. If I don't say how many, it will report close to 14 hundred records, more than fifty pages. On the requery, I add to the original request the argument to tell the server to report less records than it actually has, so you can see how it handles the drastic reduction in the number of records. You would not do that on normal operation.