Variable Columns for Datatables

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

We solved this by performing an initial server request to the database before we actually initialized the Datatable. This initial call simply performed a query to determine what the header values would be and returned them to the javascript in array format. This can either be stored in a different table or aggregated from a single table. How you store this data is out of scope for the article though, so I will move on.

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:

PDERAS - Learn Datatables - sample code

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:

PDERAS - Learn Datatables - sample code

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

The steps for providing the data from the server are very similar to the setup required for our Datatables on the javascript side. First, we need to do a call to the database to determine what our variable headers are. This query will be the same as the query from part one. Once we have the names of the headers we can use them to link the data we return to the columns on the client-side.

The great thing about Yajra’s Datatables package is that the return is a class. This means we can keep adding to it until we are ready to pass all the information back. We simply need to loop through the array of header values that we have and use the ->addColumn function to add the necessary data. For each one of these columns that we add we make the name variable equal to the header value. This way they correspond to the header definitions that we created earlier in our javascript.

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.

Having trouble getting our example working? Feel free to contact us.

2017-11-10T09:11:08+00:00