I have a table of data that contains a material, the amount of the material stored, and the date it was collected (below is an example of the data):
|------------|--------------|-----------------|
| waste_type | total_weight | collection_date |
|------------|--------------|-----------------|
| Wood | 50 | 2014-05-24 |
| Wood | 75 | 2014-06-25 |
| Metal | 150 | 2014-06-25 |
| Plastic | 20 | 2014-07-10 |
|------------|--------------|-----------------|
Using the following query:
$materialsCollected = $dm->createQuery('
SELECT SUM(efu.totalWeight) AS totalWeight, efu.wasteType, efu.collectionDate
FROM CoreBundle:EnviroFiguresUpload efu
GROUP BY efu.collectionDate
ORDER BY efu.collectionDate DESC'
);
$matColl = $materialsCollected->getResult();
Which is then put in to an array by Symfony2 like this:
Array
(
[0] => Array
(
[totalWeight] => 50
[wasteType] => Wood
[collectionnDate] => 2014-05-24
)
[1] => Array
(
[totalCO2] => 75
[wasteType] => Wood
[collectionnDate] => 2014-05-24
)
[2] => Array
(
[totalCO2] => 150
[wasteType] => Metal
[collectionnDate] => 2014-05-24
)
[3] => Array
(
[totalCO2] => 20
[wasteType] => Plastic
[collectionnDate] => 2014-05-24
)
)
Now this data is being passed to Flot.js to display a stacked bar graph. The example code I'm using is this:
<script>
init.push(function () {
// Visits Chart Data
var visitsChartData = [{
label: 'Visits',
data: [
[6, 1300], [7, 1600], [8, 1900], [9, 2100], [10, 2500], [11, 2200], [12, 2000], [13, 1950], [14, 1900], [15, 2000]
]
}, {
label: 'Returning Visits',
data: [
[6, 750], [7, 600], [8, 550], [9, 600], [10, 800], [11, 900], [12, 800], [13, 850], [14, 830], [15, 1000]
],
filledPoints: true // Fill points
}, {
label: 'New Visits',
data: [
[6, 300], [7, 450], [8, 250], [9, 100], [10, 400], [11, 300], [12, 200], [13, 850], [14, 830], [15, 1000]
],
filledPoints: true // Fill points
}];
// Init Chart
$('#jq-flot-bars').pixelPlot(visitsChartData, {
series: {
bars: {
show: true,
barWidth: .9,
align: 'center'
}
},
xaxis: { tickDecimals: 2 },
yaxis: { tickSize: 1000 }
}, {
height: 205,
tooltipText: "y + ' visitors at ' + x + '.00h'"
});
});
</script>
<!-- / Javascript -->
<div class="panel">
<div class="panel-heading">
<span class="panel-title">CO2 Savings</span>
</div>
<div class="panel-body">
<div class="graph-container">
<div id="jq-flot-bars" class="graph"></div>
</div>
</div>
</div>
The problem I have is that I want to group the data in to months, and display them in the Flot.js. However, I'm not sure how to group them up correctly. So each bar will be made up of each material, and there will be a bar for each month.
How can I group the data by month, and then be able to pass it to the Flot.js graph?
You could group it directly in the query like this. Then you would not have change the rest of the code towards the frontend much.
$materialsCollected = $dm->createQuery('
SELECT
SUM(efu.totalWeight) AS totalWeight,
EXTRACT(YEAR_MONTH FROM efu.collectionDate) AS yearAndMonth,
efu.wasteType
FROM CoreBundle:EnviroFiguresUpload efu
GROUP BY yearAndMonth, efu.wasteType
ORDER BY yearAndMonth DESC
');
By the way, you forgot to additionally group by wasteType... :)
The mysql function I use is documented here: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_extract
You need to install beberlei/DoctrineExtensions bundle and enable MONTH and YEAR functions for doctrine. Take a look at my answer here. Then you can easily group and filter your result by month
SELECT SUM(efu.totalWeight) AS totalWeight, efu.wasteType, efu.collectionDate
FROM CoreBundle:EnviroFiguresUpload efu
GROUP BY MONTH(efu.collectionDate)
ORDER BY YEAR( efu.collectionDate ) DESC, efu.collectionDate DESC
Another solution is to group result on client(in javascript) before you pass it to Flot.js
Suppose you have directly passed the query result into javascript by serializing in with json_encode and you got this:
var raw = [
{
totalWeight: 50,
wasteType: 'Wood',
collectionDate: '2014-05-24'
},
{
totalWeight: 60,
wasteType: 'Wood',
collectionDate: '2014-06-15'
},
{
totalWeight: 35,
wasteType: 'Metal',
collectionDate: '2014-05-24'
},
{
totalWeight: 70,
wasteType: 'Metal',
collectionDate: '2014-06-03'
},
{
totalWeight: 30,
wasteType: 'Plastic',
collectionDate: '2014-05-24'
},
{
totalWeight: 110,
wasteType: 'Plastic',
collectionDate: '2014-06-12'
},
];
You can now group this array by month and convert it into correct chart data:
data = {};
for (var i = 0; i < raw.length; i++) {
var wasteType = raw[i]['wasteType'],
totalWeight = raw[i]['totalWeight'],
date = new Date(raw[i]['collectionDate']),
month = date.getMonth() + 1;
data[wasteType] = data[wasteType] || {};
data[wasteType][month] = data[wasteType][month] || 0;
data[wasteType][month] += totalWeight;
}
var result = [];
for (var label in data) {
if (data.hasOwnProperty(label)) {
var item = {};
item.label = label;
item.data = [];
for (month in data[label]) {
if (data[label].hasOwnProperty(month)) {
item.data.push([+ month, data[label][month]]);
}
}
result.push(item);
}
}
Variable result is ready to be passed to flot library:
[{
'label' : 'Wood',
'data' : [[5, 50], [6, 60]]
},{
'label' : 'Metal',
'data' : [[5, 35], [6, 70]]
},{
'label' : 'Plastic',
'data' : [[5, 30], [6, 110]]
}]
You can group the values directly in your SQL query:
SELECT
MONTH(efu.collectionDate) as collectionMonth,
SUM(efu.totalWeight) AS totalWeight,
efu.wasteType as wasteType
FROM
CoreBundle:EnviroFiguresUpload efu
GROUP BY
collectionMonth,
wasteType
ORDER BY
collectionMonth
This returns an array similar to this:
Array
(
[0] => Array
(
[collectionMonth] => 5
[totalWeight] => 50
[wasteType] => Wood
)
[..]
)
Then you simply group all values by type and map them so you can use them directly with plot.js without having to format the data yet again on the Javascript side.
<?php
$sum = array();
$plot = array();
foreach($rows as $row)
{
$type = $row['wasteType'];
$month = $row['collectionMonth'];
$total = $row['totalWeight'];
if(!isset( $sum[$type] )) $sum[$type] = array();
$sum[$type][] = array($month, $total);
}
foreach($sum as $label => $data)
{
$plot[] = array(
'label' => $label,
'data' => $data,
);
}
And then you can simply json_encode the $plot array and use it with flot.js:
json_encode($plot) =>
[
{
"label": "Plastic",
"data": [
["7","20"]
]
},
{
"label": "Metal",
"data": [
["6","150"]
]
},
{
"label": "Wood",
"data": [
["6","75"],["5","50"]
]
}
]
Just keep in mind that this aggregates all months, so if you only want display the data for a certain year you should add a constraint to your sql query:
[..] WHERE YEAR(efu.collectionDate) = '2014' GROUP BY [..]