Get all values of a specific column, sorted, with their row index so I can delete specific ones?

mihomes

So the scenario is I add rows to the table every x seconds. One of the columns is a timestamp (column 0 in this case). I want to set a hard limit on how many rows are displayed as I have turned off paging. So, after I add rows to the table I compare the row count to my limit and delete the oldest rows over my limit.

What I need is a way to get the current row indexes sorted by column 0. That way I have the indexes of the oldest rows based off the timestamp and can remove them as needed.

I can't just delete the oldest row indexes as rows I add might not always be in chronological order. For example the latest row added to the table might not be the the latest row by timestamp. That is what I need to sort them by timestamp then get the row index that way.

I thought the below would do the trick, but after doing some testing the index here just refers to the sorted order... not the row indexes as in the table.

dt.column(0).data().sort().each(function(value, index) {
    array.push([value, index]);
});

console.log(array);

kthorngren

Assuming you are ordering by column 0 you can use rows().indexes() to get the indexes in order of the table. See the selector-modifier docs for more information about selecting rows based on table state. See this example:
http://live.datatables.net/dopifeho/1/edit

Kevin

kthorngren

Actually I think you might find the the row indexes are maintained in the order the rows are added to the table. Use the selector-modifier of {order: "index"} to get the rows in the order they were added. Take a look at this example:
http://live.datatables.net/tupahaho/1/edit

First it displays row index 2 then 1 then deletes index 1. It shows the same row index 2 then 1 but the rows have moved up. So you should be able to generate a contiguous array of indexes starting at 0 for the number of rows to remove, something like this to keep 20 rows:

myArray = [...Array( table.rows().count() - 20 ).keys()];
dt.rows( myArray ).remove().draw();

Didn't test this so it may need fixed but maybe something like this will work for you.

Kevin

mihomes

"are maintained in the order the rows are added to the table"

That is why I need to be able to sort them by column 0. They are not always going to be added to the table in chronological order.

My thought was if I can sort them by column 0 first then I would be able to pull the indexes of the correct ones to remove (where the indexes might not be in order).

kthorngren

The first question is how are you ordering the table?

My thought was if I can sort them by column 0 first then I would be able to pull the indexes of the correct ones to remove (where the indexes might not be in order).

See my first response. The order of the indexes is in sorted order. You should be able to get the first N number of indexes and use rows().remove() with that array.

Kevin

mihomes

I'm ordering by column 0 which is just a timestamp (integer). Thing is, when I add rows (does so every 15 seconds if new ones are found), it doesn't necessarily mean all of them are going to have a timestamp value AFTER the most recent currently in the table if that makes sense. I could be adding a row that ends up 'in the middle' of my table in terms of the timestamp value.

So using the index, in the order they are added into the table, is not going to help.

If I could do something like
test = table.rows( {order: [[0, 'desc']]} ).indexes().toArray()

I could make this work as it would be ordered by timestamp not by when it was added to the table or as is shown currently in the table (because a user could click on any column headers to sort differently). I could then just take the last x index values from this array and remove those rows.

In the second example you gave (http://live.datatables.net/tupahaho/1/edit) try adding a new row that has a name column value of 'bbbb' instead of 'a' and you will see it will not work for me.

If the rows I added in always had a timestamp value more recent than the ones currently in the table then this would be really easy to do, but they won't always be that way.

The goal is for the table to kind of behave like a 'ticker'. It checks for new data every 15 seconds and if any is found it adds it in. By me removing the oldest rows (by timestamp value not by order they were added in or the order currently displayed) I end up with a table of the 'most recent x rows' based on their timestamp value - not when they were added to the table.

kthorngren

because a user could click on any column headers to sort differently

Thanks, good to know.

This was an interesting problem. The sort() API can be used with rows().data() to get the sorted data, rows().data().sort() for example. Doing this I wasn't able to find a way to map the row data back to the row API.

@allan or @colin I have a feature request. Enable the use of the sort() like this: table.rows().sort(). The idea is to get a sorted array of row API's without the need for physically updating the table order. Maybe this is already available but I'm not sure how

I used this statement to allow for getting a sorted array of row APIs:

var orderedRows = table.rows().indexes().toArray().sort(compare);

This uses rows().indexes() along with toArray(). The toArray() is needed to use Javascript's sort() otherwise the sort() isn't called unless it is chained from rows().data().

I wrote a compare function to get the row data from the indexes passed into the function:

function compare(a, b) {

  // Get the row data (timestamp column) from the row indexes
  var timestampA = table.row(a).data()[0];
  var timestampB = table.row(b).data()[0];
.....

I think this example will do what you want:
http://live.datatables.net/yijiboda/1/edit

It starts with 4 rows then adds 2 rows with one timestamp as the lowest number and the other in the middle of the data. The lowest two timestamps (one original and one added) will be removed.

Kevin

mihomes

Tried a few things and this still won't work. orderedRows appears to be dependent on the table's current sorting so the order of the row indexes is always different. If the sorting of the columns is never changed it would work, but if it is the order is thrown off and you would be removing the wrong/different rows every time you change the sorting on any of the columns.

Going to play around with it some more and see if I can get anywhere.

kthorngren

Maybe I'm not understanding. I added a button to execute the code. No matter how I sort the table, even with both columns sorted the order seems correct.http://live.datatables.net/yijiboda/3/edit

Do you see something different?

Maybe you can update the test case to show the issue. Maybe its the tiemstamp you are using. Update the test case with your timestamp.

Kevin

kthorngren

I added two more buttons so you can and remove two rows at a time. In between you can mess with the table order.
http://live.datatables.net/dahuqoya/1/edit

Kevin

mihomes

Of course... works fine in the jsbin, but in my actual code orderedRows changes every time I sort a column. In the jsbin the order always stays the same regardless of column sorting. I have no clue at the moment why that would be... ugh. Have 1.10.21 so unless something changed with 1.10.22 as I see that is available I am clueless why there is any difference between your code behaving differently on my site.

kthorngren

Can you update the test case to closer replicate your environment to show the problem?

Have you debugged the compare function to make sure the expected data is being compared. These statments:

function compare(a, b) {
 
  // Get the row data (timestamp column) from the row indexes
  var timestampA = table.row(a).data()[0];
  var timestampB = table.row(b).data()[0];

The Datatables version isn't going to matter. The sort function is using the row index to get the row data. The actual data fetched is what is used for the sorting. The table's ordering is not in effect for this code. So debugging how the compare function is working is what's needed.

To clarify the table.rows().indexes() statement is in the table's order. But the results of the sort(compare) method has nothing to do with the table order.

Kevin

mihomes

live.datatables.net/yijiboda/2/edit

Basically what I put in my site right now using your code and to debug. Clicking of a row shows the index, clicking 'show order' shows ordered indexes by timestamp, and clicking 'add row' just inserts a new row with a random timestamp for testing.

The same functionality in my actual site/code and 'show order' changes every time I sort a column in the table. It is almost as if the 'compare' doesn't do anything in my code where it does in jsbin for some reason.

mihomes

Solved. Needed to modify to :

  var timestampA = table.row(a).data()['capture_timestamp'];
  var timestampB = table.row(b).data()['capture_timestamp'];

in my code instead of the column number. They were returning undefined before so no order was being made. Looks like this is going to work just fine! Thanks a ton for the assistance with this one!

kthorngren

Glad you found it and you are welcome. It was something new for me to look at

Kevin

mihomes

Yeah, it was certainly an uncommon need, but whole thing together is going to behave exactly like I was going for. Thanks again!