multi column sort on hidden value

kpma

Hi,

I have a column in my table that displays either the status "active" or "inactive" based on if a numeric value is zero or not. If the value is greater than zero than it shows active, otherwise it'll show inactive.
When sorting by this status column, I want it to be sorted by numeric value ascending or descending
even though I am only showing the active and inactive value.

I also need the table to support multi column sort, so that when user sort the status column, it'll sort secondary by thiis date column. For example, the group of "active" rows will then be sorted by date and the group of "inactive" rows will be sorted by date also. However, the date is sorted by the group of numeric values instead of active and inactive.
So if a group of rows share the same number, the date will be sorted within that group where I want the date to be sorted within the group of active instead. Let me know if i can clarify more.

colin

When sorting by this status column, I want it to be sorted by numeric value ascending or descending
even though I am only showing the active and inactive value.

See columns.render, modify the sort type.

I also need the table to support multi column sort, so that when user sort the status column, it'll sort secondary by thiis date column.

This can be done with columns.orderData.

Colin

kpma

Hi Colin,

I already had the render and orderData setup before, but the problem is that the secondary sort, it is group by the numeric value of the status column, but I want instead the secondary date sort to be within the group of "Active" or "Inactive".
Below is the column definition i created before:

columnDefs: [
{
name: 'status',
render: function (data, type, row) {
if (type == 'sort') {
return row.a + row.b + row.c + row.d;
}
return (row.a + row.b + row.c + row.d > 0 ? 'Active' : 'Inactive');
},
targets: 0,
orderData: [0,1]
},
{
name: 'last-updated',
render: function (data, type, row) {
if (type == 'sort') {
return data;
}
return moment(data).fromNow();
},
targets: 1
}
]

kpma

also, this is the result i'm getting, as you can see, the last updated date is not sorted correctly within the group of active rows

colin

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

kpma

I've created an example here:

http://live.datatables.net/lorekuco/2/

I want to multi column sort by status, and then last updated date. The status column should also be sorted numerically by its hidden value.

The problem I'm running into is that the last updated date is grouped within the hidden value of status (a + b + c), instead of by it's active or inactive label.

kthorngren

Thanks for the test case! Interesting. I'm not sure how you would do that. I updated your test case to show the Active and Inactive values:
http://live.datatables.net/butocise/1/edit

Looking at that output I don't see how you could sort by the Active value and sort the dates by the Active label. If you sorted the dates grouped by just the Active or Inactive label it would look like this:
http://live.datatables.net/keqewacu/1/edit

Maybe I'm misunderstanding what you want to do.

Kevin

kpma

Hi Kevin,

Yes basically i wanted to sort the status by the numeric total from highest to lowest or lowest to highest, and then the last updated date sorted accordingly within the group of active and inactive status. Not sure if it's possible, in your second example, i would still need the status sorted by their total instead of just active and inactive.

Would it be possible to custom the sort listener somehow to do the 2nd column sorting manually in javascript.

kthorngren

The problem is that you can't do both at the same time. To sort the status by numeric value then by date over the Active group you will need to re-write the dates into the table rearranging the row data. Datatables sorts by rows. It doesn't have a mechanism to individually sort each column.

For example, this is a subset of the first example sorted by number then by date:

Inactive (0)    Fri Dec 27 2019 22:03:39 GMT-0500 (Eastern Standard Time)
Active (3)  Fri Jan 17 2020 12:00:59 GMT-0500 (Eastern Standard Time)
Active (3)  Sat Jan 18 2020 22:03:39 GMT-0500 (Eastern Standard Time)
Active (6)  Fri Dec 20 2019 22:03:39 GMT-0500 (Eastern Standard Time)
Active (12) Tue Dec 01 2015 22:03:39 GMT-0500 (Eastern Standard Time)
Active (12) Tue Dec 31 2019 22:03:39 GMT-0500 (Eastern Standard Time)

Sounds like what you really want is this with the dates in order by Active/Inactive groups then by date, for example:

Inactive (0)    Fri Dec 27 2019 22:03:39 GMT-0500 (Eastern Standard Time)
Active (3)  Tue Dec 01 2015 22:03:39 GMT-0500 (Eastern Standard Time)
Active (3)  Fri Dec 20 2019 22:03:39 GMT-0500 (Eastern Standard Time)
Active (6)  Tue Dec 31 2019 22:03:39 GMT-0500 (Eastern Standard Time)
Active (12) Fri Jan 17 2020 12:00:59 GMT-0500 (Eastern Standard Time)
Active (12) Sat Jan 18 2020 22:03:39 GMT-0500 (Eastern Standard Time)

You could do this. You can use Javascript to sort the dates then use rows().every() to iterate each row. Use row().data() to update the date value for each row.

Kevin

kpma

Hi Kevin,
Thanks I’ll try out the suggestion of using rows().every() and
row().data()

kthorngren

You will probably want to remove the orderData option and use the order event. In the order you probably will want to look at the ordArr parameter to see if column 0 is ordered and if so then apply your loop to re-write the dates.

If you work on the test case we can help with any questions.

Kevin

kpma

Hi Colin,
I created a example here: live.datatables.net/lorekuco/2/edit

basically, the status column shows active and inactive base on if the total of a,b,c is greater than zero or zero. I want to multi column sort by status and then last updated date, status also needs to be sorted numerically by its hidden numeric value. The trouble i'm running into is that the date column is sorted within the group of numbers and not by the status value "active" and "inactive"

kpma

I've created an example here

live.datatables.net/lorekuco/2/edit

I want multi column sort by status, and then last updated date. The status column also needs to be sorted numerically by its hidden value.

The problem i'm facing is that the last updated date is not sorted within the group of "active" and "inactive" status, but instead is grouped within the hidden value of status which is the sum of a + b + c.

colin

I've tidied it up a bit, you only need columns, not also columnDefs. The problem with the date field is still the same though - we'll take a look and report back.

Colin

allan

Right - what's happening here isn't initially obvious, but it we add total into the output of the first column it becomes more obvious: http://live.datatables.net/lorekuco/4/edit .

The total is what is being sorted on first - not simply Active or Inactive, hence why the date order is not what you expect - it is being overridden by the first column.

Correcting that allows it to work as expected: http://live.datatables.net/yasufigi/1/edit .

Allan

kthorngren

Funny that is what I showed in my second example of my first post

Kevin

allan

Spot on as usual Kevin :-).