Heat table with server-side data

trongart

Hi everyone! There is an excellent JS library to format column data conditionally. It is discussed here: https://datatables.net/forums/discussion/comment/138180

I have been trying to run the same JS library on an ajax/php generated datatable following kthorngren's example live.datatables.net/siqoreko/1/edit but without success.

This is my attempt: live.datatables.net/siqoreko/78/edit?html,js,output

My php data produces only 2 columns with float values and for some reason the colorize function does not work on them. The data and table is displayed properly, just without the colorize function. Everything in the code is exactly the same as in the example except that I use ajax instead of a randomly generated table.

I would really appreciate any feedback!

trongart

Apologies as the live.datatables.net links were incorrect. Correct links below:
Original example from kthorngren: http://live.datatables.net/siqoreko/1/edit
My attempt: live.datatables.net/siqoreko/78/edit

kthorngren

You are getting an ajax error. Looking at the browser's network inspector for the XHR request for getData.php you will see the error response. The page is not found. The best option is to simulate the data by taking a sample of the JSON and creating a Javascript variable for the data. Then use data, instead of ajax, to load the data. See this example.

Kevin

trongart

Thank you for your feedback! getData.php is not found as it connects only to my database. It produces 2 columns of data with just numbers, but I cannot simulate it in live.datatables.net

Using the data instead of ajax option does work correctly with the colorizer as here: live.datatables.net/siqoreko/80/edit
I need to load the data from MySQL and the getData.php file so I use ajax instead. How could I apply the colorize function on ajax data or can the php/sql data be used with the data option?

kthorngren

From a Datatables perspective there is no difference in processing the data whether from using ajax or data. It could be something different within the data. Your example has var dataSet = [["1","2"],["3","4"]];. Is this the same type of data and data structure returned from getData.php? Use the browser's network inspector to see what is returned and use that for your sample data.

Kevin

trongart

From the XHR inspection the data received is as follows:

The following getData.php produces it as suggested by the datatables examples:

`<?php
// Database connection info
$dbDetails = array(
'host' => 'XXXXXXX',
'user' => 'XXXXXXX',
'pass' => 'XXXXXXX',
'db' => 'XXXXXXX'
);

// DB table to use
$table = 'products';

// Table's primary key
$primaryKey = 'product_id';

// Array of database columns which should be read and sent back to DataTables.
// The db parameter represents the column name in the database.
// The dt parameter represents the DataTables column identifier.
$columns = array(
array( 'db' => 'quantity', 'dt' => 0 ),
array( 'db' => 'price_per_unit', 'dt' => 1 )
);

// Include SQL query processing class
require 'ssp.class.php';

// Output data as json format
echo json_encode(
SSP::simple( $_GET, $dbDetails, $table, $primaryKey, $columns )
);
`

Do I need to change the array for the colorizer to work?

kthorngren

Move $("#example tbody td").colorize(); inside initComplete. Its running before the data is returned. For example:
http://live.datatables.net/siqoreko/82/edit

Kevin

trongart

Yes this works! Thank you so much for your help! Only thing is that the data colorization is evaluated based on all data together and not for every column individually as in your example. Is there a way to control this?

kthorngren

Do you mean like this?
http://live.datatables.net/niwemuce/1/edit

This uses jquery selectors to choose the column, for example td:nth-child(2) is the second column. You just need to pass into the colorize function the data you want to evaluate. See the docs for more details:
https://github.com/gryevns/jquery-colorize

Kevin

trongart

Yes exactly! You are a genius- Thank you!

trongart

When state saving is enabled with "stateSave": true and you refresh the page, the colorizing function no longer works. What could be the reason?

kthorngren

I updated the test case with StateSave and it seems to work:
http://live.datatables.net/kavugigo/1/edit

I added a 3rd data set. Search the table for 4 then click the Run with JS button. You will see the colorizing is working. Its different because colorizing wasn't updated after the search. You can use the draw event to update the colorizing for each draw, ie, search, page, sort function.

Please update the test case to show the issue. Take a look at your browser's console for errors.

Kevin

trongart

Thank you for your feedback! The colorizing is lost once I search the table for 4 (or any other number) and then refresh the page in your example too: live.datatables.net/kavugigo/1/edit

How can I redraw the table with the colorizer function after every event?

trongart

Same thing happens even when stateSave is set to false if the data has more than 5000 rows and sorting or a filter is applied -> The function stops working.

kthorngren

The colorizing is lost once I search the table for 4 (or any other number) and then refresh the page in your example too

The coloring changes because is is based on the data shown in the table.

How can I redraw the table with the colorizer function after every event?

Use the draw event to call the colorize functions that are in initComplete. Doing this the colors will change with what is displayed on the page.

Maybe a better solution is to use cells().nodes(). This will calculate the heat map based on all the data in the table not just the data shown on the page. See this example:
http://live.datatables.net/kavugigo/3/edit

The colors stay the same whether filtered or not and are the same when reloaded with stateSave. Is this what you want?

Kevin

trongart

Yes indeed - I can confirm that the approach with cells().nodes() produces the expected result. Thank you very much!

trongart

Only issue is - the colorize function stops working again when serverSide is enabled with "serverSide": true

kthorngren

With server side processing the only rows at the client are those on the page. Those are the only rows that can be run through the colorize() function. In this case you will need a server side heat map calculation. How you do this depends on your environment. I would look at a way to calculate and save the heat map colors in the DB and update that any time the data is updated. Then return the color field for each column along with the data to the client. Using createdRow or columns.createdCell you can color the cell based on the color returned.

As an example your data would change to something like this:

var dataSet = [["1","2", "green", "red"],["3","4", "white", "blue"]];

Here is a very simplistic example:
http://live.datatables.net/kavugigo/5/edit

Kevin

trongart

Thank you very much Kevin! I will look into this.

trongart

If I stay with client side processing, but with deferRender, would I also need a serverSide HeatMap? Just looking at ways to avoid that as it will be too much data to process otherwise. deferRender also stops the colorizer function from working as soon as filtering or sorting is used.

kthorngren

It looks like the colorize() function expects all the HTML nodes, loops through them to calculate the colors and sets the background-colors. The deferRender docs state this:

This option allows DataTables to create the nodes (rows and cells in the table body) only when they are needed for a draw.

Basically this means that not all the nodes will be available for the colorize() function. I would look the same as the server side processing examples.

You could refactor the colorize() function to do what I described last with the fetched data in the client. Use the ajax.dataSrc option as a function to build the colors into the data set. Then use createdRow to apply the colors.

Kevin

trongart

Thank you very much for looking into this Kevin! Appreciate your input!