update row server-side and retain its position

periklis

Hello,
I have a table where the last column is an "add/remove" button and by default it's sorted by that column. Each time the button is pressed, an ajax call is made that updates the row to the database.
My problem is that I want the table to be refreshed with the new data, but keep the row in the same place.
Currently, when the ajax returns, I do a oTable.fnDraw(false); but this makes the row to change place (due to sorting) and is removed from the current viewed page.
For example, if the table is:
data11 data12 data13! remove
data21 data22 data23! remove
data31 data32 data33 add
data41 data42 data43 add

Then if I click on the "add" for row 4, it will become:
data11 data12 data13! remove
data21 data22 data23! remove
data41 data42 data43! remove
data31 data32 data33 add

which is confusing if the row clicked on is on another page. I would want this to happen:
data11 data12 data13! remove
data21 data22 data23! remove
data31 data32 data33 add
data41 data42 data43! remove

Any ideas?

Thanks in advance

allan

I'm slightly confused by why sorting is causing this - are you sorting on the add/remove column? Can't you just disallow, or not sort on that column?

Allan

periklis

It's reasonable to happen, since I'm sorting on the last column (based on a flag from the database data) and I want it so in order for the "added" rows to appear first. Since updating a row changes this data, the sorting changes as well after the table redraws.
What I'm looking for is an elegant way to work around this. For example, ideally I would want the table to not redraw, but only update the changed row from the database. I can do this with some server-side coding and table row manipulation: the ajax request that updates the row would print out the updated row, so I would replace the existing row on the table with what the ajax request returns. But I wanted to ask if anyone else has come across this and how did he/she handled it.

As always, thanks in advance

periklis

Take this for example:
http://datatables.net/examples/api/editable.html
If I change any row's 1st column to "Gecko2", it will become "Gecko2 (server updated)" and disappear from the current view after pressing enter. Is there an established way/best practice to keep it on the current view? Or should I work around it anyway I see fit?

allan

The reason it 'disappears from view' is that according to the new data and the sort condition on the table, the row you updated would now be on the second page. So the first option is to disable sorting, or sort on something else. And the second option is to have your server-side process find out where the updated row appears in the sorting order, report that back and have the table change to view that page.

Allan

Taylor514ce

If I might chime in, I don't see this as a problem with datatables per se, but with how you've designed your application. If the sort is supposed to happen on the add/remove button column, then of course changing the value of that will affect the sort order of your table.

I think what you're after is to have the INITIAL display of the table have all "add" records at the top, "remove" values at the bottom, and that any subsequent updates NOT AFFECT this initial sort order.

I believe the proper way to handle that is server-side, by having your query order the data with an order by clause on whatever drives the add/remove column, and by also adding a calculated sort column to the result set. The Datatable should sort on this calculated column, NOT on the add/remove button column.

In short: fix this in your database and server-side code, not in Datatables.

periklis

Thank you both for your answers, I'll update my server-side code accordingly.