Creating Google Charts API DataTable From Database of Temperatures and IDs

Question

I'd like to create an Annotation Chart using pool temperature data from the database. You can take a look at the database structure here on sqlfiddle or here on rextester, but to save you the click, here's the structure I'm working with:

DROP TABLE IF EXISTS `temperatures`;
DROP TABLE IF EXISTS `pools`;

CREATE TABLE `pools` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `temperatures` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pool_id` int(10) unsigned NOT NULL,
  `temperature` double(8,1) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `temperatures_pool_id_foreign` (`pool_id`),
  CONSTRAINT `temperatures_pool_id_foreign` FOREIGN KEY (`pool_id`) REFERENCES `pools` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3173 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `pools` (`id`, `name`, `created_at`)
VALUES
    (1,'Pool #1','2017-04-08 22:48:03'),
    (2,'Pool #2','2017-04-08 22:48:03'),
    (3,'Pool #3','2017-04-08 22:48:03');

INSERT INTO `temperatures` (`id`, `pool_id`, `temperature`, `created_at`)
VALUES
    (31,1,100.1,'2017-04-09 02:44:56'),
    (32,2,104.2,'2017-04-09 02:44:56'),
    (33,3,97.0,'2017-04-09 02:44:56'),
    (34,1,100.1,'2017-04-09 03:00:04'),
    (35,2,98.4,'2017-04-09 03:00:04'),
    (36,3,96.6,'2017-04-09 03:00:04'),
    (37,1,100.1,'2017-04-09 03:37:13'),
    (38,2,101.8,'2017-04-09 03:37:13'),
    (39,3,96.4,'2017-04-09 03:37:13'),
    (40,1,100.1,'2017-04-09 04:00:04'),
    (41,2,101.8,'2017-04-09 04:00:04'),
    (42,3,96.5,'2017-04-09 04:00:04'),
    (43,1,100.1,'2017-04-09 05:00:04'),
    (44,2,101.8,'2017-04-09 05:00:04');

Okay, so essentially, I'm created a controller that will return properly formatted JSON for use with ajax and google.visualization.DataTable(), like this:

var jsonData = $.ajax({
    url: "/data/pool-temperature-timeline",
    dataType: "json",
    async: false
}).responseText;

data = new google.visualization.DataTable(jsonData);
chart.draw(data, options);

Of course, looking at the documentation, the annotation chart is expecting things to follow this format:

var data = new google.visualization.DataTable();
data.addColumn('date', 'Date');
data.addColumn('number', 'Kepler-22b mission');
data.addColumn('string', 'Kepler title');
data.addColumn('string', 'Kepler text');
data.addColumn('number', 'Gliese 163 mission');
data.addColumn('string', 'Gliese title');
data.addColumn('string', 'Gliese text');
data.addRows([
    [new Date(2314, 2, 15), 12400, undefined, undefined,
                            10645, undefined, undefined],
    [new Date(2314, 2, 16), 24045, 'Lalibertines', 'First encounter',
                            12374, undefined, undefined],
    [new Date(2314, 2, 17), 35022, 'Lalibertines', 'They are very tall',
                            15766, 'Gallantors', 'First Encounter'],
    [new Date(2314, 2, 18), 12284, 'Lalibertines', 'Attack on our crew!',
                            34334, 'Gallantors', 'Statement of shared principles'],
    [new Date(2314, 2, 19), 8476, 'Lalibertines', 'Heavy casualties',
                            66467, 'Gallantors', 'Mysteries revealed'],
    [new Date(2314, 2, 20), 0, 'Lalibertines', 'All crew lost',
                            79463, 'Gallantors', 'Omniscience achieved']
]);

var chart = new google.visualization.AnnotationChart(document.getElementById('chart_div'));

Right, so that's the setup, now comes the question. What's the best way to organize the data so that 1.) there is always temperature data for pool 1, 2, AND 3 for the same datetime (I worry the dataset might not be complete for the given timestamp)? Should I organize it starting at the SQL layer by using some clever query? Or do I organize it in the controller by using a bunch of foreach loops? This is the goal I'm striving for:

$dataTable->addRow(['created_at', 
    'temperature1', 'title1', 'text1',
    'temperature2', 'title2', 'text2',
    'temperature2', 'title2', 'text2',
]);

I could see the clever query would be a nice way to go to avoid doing a bunch of logic and foreach loops in the controller. Maybe if the data was organized in the columns, like:

created_at, pool_1_temperature, pool_2_temperature, pool_3_temperature
------------------------------------------------
2017-04-09 02:44:56, 100.1, 104.2, 97.0
2017-04-09 03:00:04, 100.1, 98.4, 96.6
2017-04-09 03:37:13, 100.1, 101.8, 96.4

Then I could pretty easily go through that and create the DataTable. I'm not sure how to do this in MySQL though or even if it's a good idea.

Thanks for taking the time thus far and thanks in advance for any help. I hope I was clear enough.

PS. I guess the closest thing I've come across so far is Mysql query to dynamically convert rows to columns. I'm going to play around with this some more...

Answer 1

so long as the x-axis (first column) is a date,
you shouldn't need to worry about...

there is always temperature data for pool 1, 2, AND 3 for the same datetime

the chart should be able to work it out

as such, you could use a query similar to the following...

select
  created_at,
  case when
    pool_id = 1
  then
    temperature
  else
    null
  end pool_1,
  case when
    pool_id = 2
  then
    temperature
  else
    null
  end pool_2,
  case when
    pool_id = 3
  then
    temperature
  else
    null
  end pool_3
from
  temperatures

i wasn't able to get either SQL link provided to work,
so i wasn't able to verify the sql

i'm not sure if returning null will work

Answer 2

In order to be sure the data is dynamic, in case another pool was added in the future, I decided to go with a padded array using array_pad() and loop through the temperatures dataset, sorting as I go. I also used Lavacharts as that made dealing with Google DataTables easy. So, here's my code (note, more work required for adding annotation fields):

$dataTable = \Lava::DataTable();
$dataTable->addDateTimeColumn('DateTime');

// Add data column for each pool
$pools = \App\Pool::get();
foreach($pools as $pool) {
    $p = "Pool $pool->id";
    $dataTable->addNumberColumn("$p Temp");

    // TODO:  Create annotate fields for min and max temperatures
    // For this, we'll need to do some clever padding using array_pad()
    // and more clever index incrementing in the for() loop below.
    // Perhaps it's best to calculate and prepare in the temperatures query?
    //$dataTable->addStringColumn("$p Title");
    //$dataTable->addStringColumn("$p Text");
}

// Gather all the temperature data we wish to display.  A year ought to be enough.
// At one hour updates, that makes for about 8,766 datapoints.
$temperatures = \App\Temperature::where('created_at', '>=', \Carbon\Carbon::now()->subYear())
    ->orderBy('created_at', 'desc')
    ->orderBy('pool_id', 'asc')->get();

// Grab all the timestamps and organize into an array
$created_ats = \App\Temperature::groupBy('created_at')->pluck('created_at');

// Let's go through each datetime field and collect all temperatures recorded on that datetime.
// Then, let's store those temperatures into the appropriate index of the data row.
foreach($created_ats as $created_at) {
    $dataRow = [$created_at]; // Start the array off by adding date to beginning
    $dataRow = array_pad($dataRow, 1 + count($pools), null); // +1 to account for $created_at column
    //$dataRow = array_pad($dataRow, 1 + (count($pools) * 3), null); // TODO: multiply by 3 for annotation fields

    // Start going through each temperature recording and assign to proper spot in dataRow array
    // If temperature is not found for the datetime, the array_pad() above already accounts for null
    // in that index.  Note, the created_at comparison only accounts for the hour, not seconds or minutes.
    // TODO: Implement min and max temperature annotations.
    //$maxTemperature = 0;
    //$minTemperature = 999;
    foreach($temperatures as $temperature) {
        // TODO: Implement min and max temperature annotations.
        //$maxTemperature = ($temperature->temperature >= $maxTemperature) ? $temperature->temperature : $maxTemperature;
        //$minTemperature = ($temperature->temperature <= $minTemperature) ? $temperature->temperature : $minTemperature;

        // Compare date and hour, then assign to appropriate index of the data row according to pool id.
        // ie.  Pool ID #1 needs to be placed in [1], Pool ID #2 in [2] and so forth. Remember, [0] is date.
        if ($temperature->created_at->format('Y-m-d H') == $created_at->format('Y-m-d H')) {
            for ($i = 1; $i <= count($pools); $i++) {
                if($temperature->pool_id == $i) {
                    $dataRow[$i] = $temperature->temperature;
                }
            }
        }
    }

    // We've gone through all temperatures for this created_at datetime.  
    // Add the resulting dataRow to the dataTable.
    $dataTable->addRow($dataRow);
}   

// What we're left with is a bunch of rows that look like this!
// TODO: Add annoation fields for min and max temperatures.
// $dataTable->addRow(['created_at', 
//  'temperature1',
//  'temperature2',
//  'temperature2'
//  ]);
$jsonData = $dataTable->toJson();

// At this point, return $jsonData for use with google.visualization.DataTable(jsonData);
// Or, cache it and then return it, or whatever.

I would recommend caching the data, as it seems to take a little bit of time when rendering in the view (~1.9s). So, maybe it's not the quickest method out there, but it does the trick for me. It'd be interesting to dig in further and find other optimizations. For now, I'm happy with it.