Columns order and show/hide dynamically

lenamtl

Hi,

I'm working on a PHP application.
The admin can change the order of column and decide if it will be displayed or not, on each table.
The settings for each columns are saved in database.
I'm looking for the best way to handle the column order & show and hide option dynamically.

Thanks

lenamtl

Hi, here is my temporary solution:

The table display all columns by default
The admin can save the settings for each datatables table in DB
to change order and visibility.

To have less MySql queries as possible I end up with this.

I'm saving colReorder order in DB per table
I'm saving colVis columns in DB per table

Let me know if this a better way to do it.

jr42.gordon

Check out https://datatables.net/reference/option/stateSave and all associated options including: stateLoadCallback, stateSaveParams, stateSaveCallback. You can use idea behind this functionality. The settings that are saved is a JSON string so you can save that to the database.

Although I want to pose a question to you, why not allow the users to select the column order and visibility that they like and allow them to save it?

lenamtl

Hi,

I'm already using stateSave with localstorage for the end user.
I'm not using the DB for that yet, but this will probably the way to go because localstorage is only for a specific browser. If you have a working example on how to achieve that will be appreciated.

(this is a requirement).
The system will allow admin to select which colums will be visible and the order of the columns for each tables which will be the default for enduser. (this need to be saved in DB)

Then enduser will be able to change the default admin settings if they want for more flexibility (except they should no be able to see hidden column)
This may be use for export, let say a user want to export or print only 3 columns things like that. (this was not in the requirement, but I need to make it the more flexible as possible and datatable is more flexible that I thought).

jr42.gordon

So if I haven't used your page yet, then my initial table setup will be what the admin set. I can then override this to whatever I want. Albeit using only what columns are visible. Any subsequent time I visit the page, it will use my config. Is this correct understanding?

What if I have been using the table configured how i like but the admin decides to show a previously hidden column? Does my config get overridden with admin? That would irritate the crap out of me if I was a user.

I have thousands of users using my DataTables and whenever I added a new column it would wipe their config and revert to default. They hated this. So I spent entire week figuring solution to prevent this.

jr42.gordon

localstorage if the default location but you could easily override the callback to save to a DB with the key being user ID.

lenamtl

No the admin don't overwrite the user config directly, but the user will have to clear the localstorage to get the update... I don't know if this possible to update automatically the local storage based on the new db settings without a complete reset ...

The colvis column should match the visible column otherwise user could show hidden column.... Unfortunately I realized that colvis column setting is use only for the Menu selection not for the column to be shown, I thought I would be able to use it like colReorder columns. They don't work the same.
Is there another colvis setting that can show only a certain column list.
or the only solution is to use colunmdef to save the column visibitily?

Using the stateSave with database this will require the user to manually save the state by clicking a button, what I like with localstorage is this is automatically save-update.

When saving the state to DB which format do you use?
Do you save all table settings in one db column or separately.

jr42.gordon

The way my application works is the state save is saved to localstorage. Everytime a user first logs on i check if a config for a table exists in localstorage, if it doesn't then I check DB and load it if it exists. If it doesn't exist there either, then default config is applied and saved.

After every DataTable is configured, I make an ajax call which sends all DataTable configs to DB to be saved. Now the user can pull up my application from any browser on any computer and work without having to spend time re-setting up their tables.

jr42.gordon

If you want to exclude certain columns from colvis, give them a class 'not-colvis-col, then configure your button as follows

{
                extend: 'colvis',
                columns: ':not(.not-colvis-col)'
}

lenamtl

ok thanks to share the logical of your application this help me to rethink mine.

About colVis the problem I have
columns: '1,2,3,4,5', it use only for menu selection it is working ok
but I can't use this to show the column on page load
I thought it was possible and it's seems not.

compare to colReord
order: [ 1,2,3,6,7 ],
this will overwrite the default value on page load

jr42.gordon

If you want to dynamically change the visibility or order of columns, use https://datatables.net/reference/option/columns.visible and https://datatables.net/reference/api/colReorder.order()

lenamtl

Thanks a lot for your patience
I will make more tests based on your suggestions.