Change default ordering and column visibility conditionally?

mihomes

var dt = new DataTable('#myTable', {
    "columns": [
        { "data": "one" },
        { "data": "two" },
    ],
    order: [ [0,'asc'] ]
});

Given the example. Is there any way to conditionally change the default order? Same goes for column visibility?

I've made the example above as simple as possible to illustrate my use case. Depending on external data... on each draw including the initial page load draw... I can have two situations :

1 - Both columns one and two are shown and the default ordering is 0, asc

2 - I need to hide column one (visibility:false) and the default ordering should be 1, asc

This can change on each draw of the table depending on the existence of a single external value - let's call it var condition. It has been a long night at the desk so this is probably easy and my brain is just fried.

I am thinking I can conditionally do this in the dt creation which would work for the initial draw. Every draw after that I could make changes in the preDrawCallback by checking if( typeof dt !== 'undefined' ) so it would only run after the initial draw.

Where I am stuck is users can sort by other columns and can also sort by multiple columns at once. This could only happen after the initial draw so in the preDrawCallback I 'think' I need to get a list of the order by that will be sent in for the draw. That way, if I am in scenario #2, I can check if column 0 is in the order list and remove it.

So, after that long novel... in short, is there a way to get the order by information that will be sent in for a draw IN the preDrawCallback so I can modify it before the draw?

mihomes

New day and messing around with this more... I see now that I can get the order by results with dt.order().

With that said, is there no way to change the orderable true/false for a column after dt has been created?

For instance, in the PreDrawCallback I'd like to do something like dt.column(1).orderable(false) to prevent ordering on that column by the user where it was previously allowed - or vice versa depending on the situation.

allan

With that said, is there no way to change the orderable true/false for a column after dt has been created?

Currently no. It isn't a request that has come up all that often, so I've never put the time into doing it. You could unbind the default listeners and then rebind with order.listener().

It would actually be too late to do it in preDrawCallback anyway - the sort (and filter) has already happened by that point, there is just the draw remaining.

1 - Both columns one and two are shown and the default ordering is 0, asc
2 - I need to hide column one (visibility:false) and the default ordering should be 1, asc

What is the logic for deciding that?

You could use a simply ternary:

order: firstColumnHidden ? [ [1,'asc'] ] : [ [0,'asc'] ]

Allan

mihomes

The use case is a column showing distance (from point a to point b). Point a being the user's location that I get from the browser and point b coming from my database. I cannot calculate the distance in my db query if I don't have the user's location so in that situation I was just going to return 'n/a', but the key thing is I need to turn ordering off for that column because it will error out in my db query trying to sort on something that does not exist. This is all serverside with php grabbing results from my db.

On the flip side, if I do have the user coords then there will be a value in the distance column and that is the column I want to order by default and allow ordering on (showing closest results to the user first or 'asc'.

I was hoping I could conditionally turn on/off ordering on a column as that is the only thing preventing me from accomplishing this.

allan

Sounds like you are using server-side processing? Could you just have the script ignore a request to sort on that column if you don't have the required information? You could perhaps use preXhr to modify the query send to the server and remove the ordering command if that condition is met?

Allan

mihomes

order: firstColumnHidden ? [ [1,'asc'] ] : [ [0,'asc'] ]

Only works for the initial - not any afterwards so it won't do anything for me in this situation. One of the first things I did hoping it would work 'all the time'.

mihomes

I managed to figure out a solution for this. I am ordering by both columns by default. When I do not have data in column 0 I just return an empty string from my db query for that column.

So when column 0 has data it orders by it then column 1.

When column 0 doesn't have data it still orders by it then column 1, BUT since every row has an empty string for column 0 that ordering has no effect - it is basically then ordering by column 1.

Had to think this one through, but in the end it works how I wanted.

allan

Only works for the initial - not any afterwards so it won't do anything for me in this situation. One of the first things I did hoping it would work 'all the time'.

I don't understand I'm afraid. I thought you were asking about how to set the order when initialising the DataTable? order is how that is done. If you need to set the order after initialisation, use the order() method.

Good to hear you have a solution though.

Allan