Most developers have used Datatables when they want to display large amounts of data, but what happens when the structure of that data is different depending on the search parameters provided? How do you handle data that can have variable columns and column names all in the same table?
We recently ran into this problem when we were developing an application where the user had the ability to add and remove columns to the table as they worked with the system. This required us to use the database information to dynamically build the table as the user requested new data.
Our application’s backend was built on Laravel, a great open-source PHP solution. Because our data was dynamic we needed to implement server-side processing for our Datatables instances. Luckily for us, there is already a Laravel package that handles all of that processing: Laravel-Datatables. This meant that we didn’t need to worry about structuring the data for Datatables, except for the fact that the data is dynamic.
So, how do we initiate Datatables when we don’t know what the data will look like beforehand?
Setting up Dynamic Headers for jQuery Datatables
Now that we had an array of the dynamic headers we could create our table and column definitions. The table was built using simple jQuery append methods as we had the tables scaffolding already created in our HTML. It looked something like this:
We had some default columns that needed to be included in the table, but once we had the dynamic values from the database we simply appended them to the row. One thing to note is that we appended the headers to the table before Datatables was ever initialized.
As I mentioned before, the array of values that we retrieved was also used to build the column definitions that Datatables requires to build the table. The function that we used to build out the column definitions looks something like this:
The function that we built for this task is a little more complicated because we had a few of the required columns that we needed to account for. In a situation where all the columns are dynamic, simply looping through the array and creating the objects that Datatables requires for its definitions does the trick.
Now that we had all the definitions defined and a table built, we needed to initialize Datatables. This requires some back-end work so let’s take a look at the Laravel-Datatables functions that we used.
Note: To use this, make sure to turn server-side processing on when initializing Datatables.
Providing the Dynamic Data to Datatables through Laravel
Once all of the necessary columns have been added we can return the Yajra Datatables object and Datatables does the rest.
What if the User wants to Refresh the Data or it has Different Search Criteria?
For our case, we had the tables separated by year. This meant that the table could have different columns based on the year that the user had selected. Thankfully, Datatables has built-in methods that allow you to remove all Datatables related items from your table.
So if you are wanting to refresh the table, simply call the destroy() method on your Datatable, remove all the headers and rows so that the table is just the scaffolding again, and then call all the functions that I just outlined above.
We had some trouble figuring out the solution to dealing with dynamically created Datatables, but we hope this blog will ease some of your pain if you find yourself in the same situation.