How to get from the server only what is being displayed.

Rpiechura

So what I'm wondering, is if there is a way to set up data tables so that it still shows the proper pagination (for example if there are 57 records and 10 per page, it'd still show the page 1, page 2 ect buttons) but only make a request for the data that would be displayed. IE only make a request from the datasource for entries 1-10. The end behavior that I'm after is to make it so that any time someone does anything with the data, be it sorting the columns, making a query, switching pages, anything that it makes a call to the server to get the information with the proper information. A big part of the reason is what you've described on the server-side processing example on the front page, but I'd rather that the client tells the server only what it wants so that the server code can be reduced.

My thought was to basically query the datatables for things such as iDisplayLength and iDisplayStart to get all the information that I need to just make an ajax request myself and than redraw the table with new information, but I'm not entirely sure what properties I'd even need to be able to do that so I'm hoping there is a more clean way of doing something like this. Any help would be greatly appreciated.

allan

It sounds like you want exactly server-side processing :-).

I don't understand this point though:

> but I'd rather that the client tells the server only what it wants so that the server code can be reduced.

Server-side processing does tell the server what it needs to be displayed. See: http://datatables.net/usage/server-side

Allan

Rpiechura

I saw that obviously, but I'll take another look. Thanks for your quick response.

Rpiechura

We got what we needed by using oSettings and grabbing the data we needed to change the server call to use the oSettings.jqXHR ajax call. Without going into too much detail the idea was to make our requests RESTful compatible so that even if we didn't control the server we could still get the data into our table. We did end up using server-side processing, but in the client to change the request and response appropriately so that we never had to touch the actual server response code.

Thank you again for your response, it made me take a second look at that page which made me find what I eventually needed.

Rpiechura

I now have a different question though. I'm using server side processing like I already said, and we're building our prototype using opencongress.com which uses a RESTful api. They have the data request set up so that you can request specific pages, so by default we have a request that looks something like:

http://api.opencongress.org/people/senator?per_page=10&page=1

for the first page, the loading works just fine, but requesting the last page (1260) takes awhile. I'm aware that is on their side, not yours but it opens up a troubling bug. If you hit last, and than first on the pagination than it makes both requests async and since the first page takes less time to load, you first see that data return, and than it changes to the last page. However the info and pagination still shows that you're on the first page. Any ideas how to fix this?

Below is my code to request and receive the data. The displayItems() call is just putting the JSON data into a JS array.
[code]
oSettings.jqXHR = $.ajax({
dataType: 'json',
url: sSource,
accepts: "application/json",
headers: {
Accept: "application/json"
},
success: function (json) {
displayItems(json);
json.iTotalRecords = json.total_pages*oSettings._iDisplayLength;
totalRecords = json.total_pages;
json.iTotalDisplayRecords = json.total_pages*oSettings._iDisplayLength;
json.sEcho = oSettings.sEcho;
json.aaData = aDataSet;

fnCallback(json);
},
});
[/code]

allan

I suspect your client-side manipulation is the error here. I think you probably need a closure on sEcho - i.e. assign it to a variable at the top of the fnServerData function and then assign that variable to json.sEcho . The reason being that oSettings.sEcho will have moved on by the time the second return comes back.

Allan

Rpiechura

That did expose a bug of sorts in the code we had, in that the sEcho doesn't live in oSettings, it's in aoData[0].value, but fixing that had no effect on the issue. The code that I now have is pretty much the same as before so I'll only show snipets of the changes I made so you can see what I did and make sure they are what you were talking about.

[code]
"fnServerData": function (sSource, aoData, fnCallback, oSettings) {
echo = aoData[0].value;
...
success: function (json) {
displayItems(json);
json.iTotalRecords = json.total_pages * oSettings._iDisplayLength;
totalRecords = json.total_pages;
json.iTotalDisplayRecords = json.total_pages * oSettings._iDisplayLength;
json.sEcho = echo;
json.aaData = aDataSet;

fnCallback(json);
},
[/code]

I also believe I may have mis-informed you about the behavior that we saw before. When I hit last and than immediately hit first, it shows the processing text above the table as usual, the information at the bottom and the pagination change to the first page (since that request came back first) and the information in the table will flash the first page information, than jump to the last page information. So the data in the table itself show the data in the order it gets the packets, but the pagination and information text only change once(whichever one came back first).

I'm curious if there is a way once a single request is made, to force the table to disable navigation until the request comes back. That would eliminate the race condition that it appears we have going on here, or if there is something else to make sure that each request gets processed properly by putting a closure of sorts around them.

Rpiechura

To make it much more clear, I went ahead and made a JS fiddle so you can experience the issue yourself, I didn't bother adding any CSS, so it's pretty crude, but I'm sure it'll let you experience the problem yourself and hopefully find an easy solution.

http://jsfiddle.net/RM3W2/1/

allan

Oops - I'd forgotten sEcho wasn't in the settings!

> I'm curious if there is a way once a single request is made, to force the table to disable navigation until the request comes back.

Yes - you can use the processing display element ( `dataTables_processing` ) to cover the entire table or the entire page using CSS. That way any clicks on the table controls will be captured by the processing element and nothing will happen.

The sEcho variable is intended to stop the earlier requests from overwriting later ones, if they take longer to return. That is the variable to stop the race condition.

Allan

Rpiechura

Could you please provide me a sample of how to cover the entire table in such a way that it is essentially a modal dialog. I'm not entirely sure how to do that myself, but it would solve the problem.

Also the latest version of the fiddle is http://jsfiddle.net/RM3W2/5/

Rpiechura

On a completely different subject, is there anyway that you could update the nightly ColVis to have the require factory wrapper that Datatables has? Or is there a reason that you don't have that going on?

allan

ColVis - it does: http://datatables.net/download/build/dataTables.colVis.nightly.js?_=4c7fa7e11152715969bcdc821ce0fec1 . Unless I'm missing something?

I'll try to make some time tomorrow to make a sample of the CSS require - but hard pushed at the moment for time.

Allan

Rpiechura

Ah, I see, you put it at the bottom. I just assumed it was going to be at the top. That's my bad.

I really appreciate the help and I understand that you're not always going to get back to me the same day, so don't worry about being extra punctual with me. =P

Rpiechura

Is there a way to re-draw a table without making a new request when using server side processing?

I'm working on making the table size directly correlate with the browser size and currently I'm calling a length change method to update the display length and display start. The information is being updated just fine, however I obviously have to redraw the table in order to see it, and as best as I can tell this is causing a new request to be fired off since I'm doing server side processing.

allan

Correct - ColVis makes this call when the column visibility is changed: `that.s.dt.oInstance.fnDraw( false );` - so when using server-side processing, yes, it will always make an Ajax call to the server. You could use pipelining if you want to avoid that call: http://datatables.net/release-datatables/examples/server_side/pipeline.html

Allan

Rpiechura

Once again, I really appreciate all the help you've given me to this point. I'm not entirely sure if I should keep asking questions in here or just start a new thread for each question (I'm starting to think the latter) but I have another question.

I am trying to get the individual column filter to work the way http://datatables.net/release-datatables/examples/api/multi_filter.html works. However the example code you have has a pre-defined table instead of an auto-generated one. How do you get the table footer / filter fields to be generated when you initialize the table?

I've also seen the http://jquery-datatables-column-filter.googlecode.com/svn/trunk/index.html plugin, but I can't seem to get that to work at all and I'm wondering if it just doesn't work with 1.9.4 or if it's something I'm doing wrong, but I've tried something as simple as
[code]
$(document).ready(function(){
5. $('#example').dataTable()
6. .columnFilter();
7.});
[/code]
and it doesn't work.

Rpiechura

Okay, so I got it to work if I just hardcode the table footer and use the code you provided with the multi-filter example. However I'm still curious if you can do it all auto generated. Thanks in advance for making it so easy to actually implement this myself, the plugins would of been nice if they worked, but it didn't actually take THAT much work to make my own. Excellent job making it extendable.

Rpiechura

I figured out how to make the footer auto generate. The parameters are element and columns. I'm using knockout to find the element that the table is being generated from and the columns are coming from jquery and the .data() selector:
[code]
var columns = $(element).data('columns').split(", ");
[/code]

this is used in the html by calling:
[code]
data-columns="Id, First Name, Last Name, Birthday"
[/code]
The 'columns' in data-columns can be anything. With that and all set we can call our helper function and pass it the proper data.

[code]
function createFooter(element, columns) {
var footer = document.createElement('tfoot');
var tr = document.createElement('tr');

jQuery.each(columns, function (i, value) {
var th = document.createElement('th');
var input = document.createElement('input');

input.setAttribute("type", "text");
input.setAttribute("name", "search_" + value);
input.setAttribute("value", "Filter " + value);
input.setAttribute("class", "search_init");

th.appendChild(input);
tr.appendChild(th);
});

footer.appendChild(tr);
element.appendChild(footer);
}
[/code]

This attaches onto the table that is generated the usual way and gives an input for each column that are specified in one line of html code which obviously makes it very re-usable. I then attached the multi-column filtering that you already suppplied at http://datatables.net/release-datatables/examples/api/multi_filter.html and Bam! We have a fully functional auto generated footer with filtering!

Thank you very much for all the help in this thread. When I come up with another question I'll be sure to post in a new thread for each unrelated question. I hope that this little bit of code is helpful to someone else.