Is it possbile to make the built in search work only over visible columns ?

desperado

I can build a test case if needed but I think this question is straight forward enough to not need it.

I have two columns in my DataTable that are only needed by certain users. So I make the non-visible. The data in these two columns is similar to the data in two of the primary columns used by the remaining users.

The problem is a search for the unique strings in these columns results in returned values regardless of the visible status. This is non-intuitive to the user because they don't realize the other columns exist when they are hidden. It also prevents the results we want to return.

I thought there must be a way to make search work over just the visible columns but when I search for this I get old answers from the older data table versions.
https://datatables.net/forums/discussion/45016/how-to-search-only-visible-columns
I tried these techniques but they don't work correctly with the current version.

Is there a way to tell the search feature to only consider visible columns in the current version?

kthorngren

I would say that the options in that thread are still current. Are you setting columns.visible on initialization or are you doing something dynamic?

One thing I noticed recently is it seems Datatables set the filter data to an empty string, ie, "", for the column set to searchable: false. If you are dynamically setting column visiblity you might be able to use orthogonal data and set the filter value to an empty string if the column is hidden. The question si how to determine if that column is hidden. If needed please build a test case and we can take a look.

Kevin

desperado

@kthorngren OK I will work on a test case. Might take me a bit.

Not sure what you mean by dynamically setting columns visibility. I am using the Visibility Button and saved State so in a way it's dynamic. I can initialize searchable to false but once a user changes their visibility when they return that initialization would be wrong. So I think I need to write code to set searchability when the table initializes and when the visibility buttons are used.

I tried this solution from the thread https://datatables.net/forums/discussion/45016/how-to-search-only-visible-columns

$('span.toggle-vis').on( 'click', function (e) {
        this.classList.toggle('columnHidden');//toggling a css class that will indicate the field has been hidden.
        let colId = $(this).attr('data-column');
        let column = table.column(colId);
        // Toggle the visibility
        let columnsettings = table.settings()[0].aoColumns[colId];
        column.visible(!column.visible());
        //this will toggle the column's searchability
        columnsettings.bSearchable = column.visible();
        //reset the table's searchability settings to add or remove the toggled column
        table.rows().invalidate();
        table.draw();//this will rerun the last search with the visible fileds only
    });

I think the problem is the button visibility doesn't use span.toggle-vis anymore so this code is never triggering.

kthorngren

You could try the column-visibility instead. However that code is changing the settings directly. These are meant to private and aren't guaranteed to be the same in future versions.

I decided to try the code and it seems to work. It sets the filter data to "" as I described above. You can look at the aoData property of the settings object to see this. Its a much simpler solution with the column-visibility

http://live.datatables.net/tevupudi/1/edit

Just keep in mind the settings object may change at some point. Maybe DT 2.0 will allow for dynamic changes to the columns.searchable option.

Kevin

desperado

@kthorngren Very cool that works perfectly. Thanks!

desperado

@kthorngren OOPS I spoke too soon. Your solution doesn't work with stateSave enabled.

Test Case
http://live.datatables.net/cojuxuza/1/edit

1) Open the test case
2) Search for "ang"
3) Note Angelica Ramos and Jennifer Chang are the only matchs
4) Make Name column not visible
5) Note that no rows match now (Correct)
6) Reload the page
7) Now Angelica Ramos and Jennifer Chang are returned (ERROR)

Is it possible to write an init function which will check the visibility and set the state much like you are doing on the column-visibility.dt trigger.

I tried writing one but I am unable to find an api call to set searchable and I don't know how to get a handle on settings like you used in your method.
http://live.datatables.net/cuwanaqu/1/edit

desperado

@kthorngren !!!!!!!!!!!!!! CORRECTION. !!!!!!

The test case is messed up. This keeps happening to me when I use live.datatables.net. I make changes to the test case, save template and lock the case and share and the case goes back to old version which is incorrect! So frustrating I don't know what I keep doing wrong. I prefer jsfiddle so here is the correct test case in jsfiddle.

https://jsfiddle.net/gouldner/ahuwvtxr/

1) Open the test case
2) Search for "ang"
3) Note Angelica Ramos and Jennifer Chang are the only matchs
4) Make Name column not visible
5) Note that no rows match now (Correct)
6) Reload the page
7) Now Angelica Ramos and Jennifer Chang are returned (ERROR)

This is my attempt to fix but I can't find a method to set searchable on a column.
https://jsfiddle.net/gouldner/y76faz5g/

desperado

@kthorngren !!!!!!!!!!! UPDATE. !!!!!!!!!!!!!

I solved it!

table.context[0].aoColumns[colIndex].bSearchable = state;

https://jsfiddle.net/gouldner/ryeo2t6h/

kthorngren

Glad you got it working.

Not sure what the Save template option does with live.datatables.net. When you start editing a live.datatables.net example it automatically saves, the URL number will change. Unless you get the error stating something about currently viewing someone else's live stream and cloning your own. In this case just use the Clone option. Just post the new URL after making the changes.

I find live.datatables.net easier to use than jsfiddle

Kevin

desperado

@kthorngren I wasn't sure what save template did either.

I like live.datatables.net but it has happened to me so many times that I post and later find an older version when I open my link. It's weird, I don't know what I keep doing wrong, haha.

I have one more question for you.

In my example I placed my searchable init code after the create of the datatable variable and that is working but it feels wrong. I worry there could be race condition if there is a longer table load time.
(From above https://jsfiddle.net/gouldner/ryeo2t6h/ )

I tried placing the code in initComplete but it fails with error "Uncaught TypeError: Cannot read properties of undefined (reading 'columns')"
https://jsfiddle.net/gouldner/vztg397y/

Should I just leave it how I have it in the first example?

kthorngren

I like live.datatables.net but it has happened to me so many times that I post and later find an older version when I open my link. It's weird, I don't know what I keep doing wrong, haha.

As I said the test case will be auto saved with a new URL. So if I edit http://live.datatables.net/cuwanaqu/1/edit the number increment to http://live.datatables.net/cuwanaqu/2/edit or whatever is next. Make sure that is happening. Or if you get the message about Cloning make sure to use the Clone option.

Uncaught TypeError: Cannot read properties of undefined (reading 'columns')

Datatables initialization is complete in initComplete but Datatables has returned the API to the table variable yet. Its not documented well but to get an instance of the API in most callbacks use this.api(). Like this:
https://jsfiddle.net/ag0j5nm2/

If you are using ajax then you will need to use initComplete for any final Datatables setup. Otherwise, like you mentioned, the data won't be there when the code executes.

Kevin

desperado

@kthorngren Thanks, I am using ajax and surprisingly my method is working but I will switch to your design in the example because it is clearly the right way to do it.

Regarding live.datatables I will try to learn to get it right, haha. I do get the clone message a lot which is weird because it tells me I am using someone else's version when I wrote it and am currently modifying it. That's probably the issue. I think part of the problem is many of my issues are related to stateSave so I have to keep reloading the page to test and I think that leads to the clone warning issues.....

Thanks so much for your help! Have a great weekend!

kthorngren

I don't think the liv.datatables.net system keeps track of who created the test case. You can open a new window and copy the url into it and get the Clone message.

To test stateSave just click the Run with JS button.

Kevin