Group data by month and display it in a multidimensional array using Symfony2, Twig and Flot.js

Question

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?

your `ORDER BY efu.collectionDate DESC` should already do this.. no? — ddavison, Jul 24 '14 at 21:40
Can you review your answers please? Right now the winning answer is both incorrect and incomplete. Thanks. — knrdk, Aug 02 '14 at 02:39

score 2 · Answer 1 · edited May 23 '17 at 12:25

2

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

edited May 23 '17 at 12:25

Community

1
1

answered Jul 25 '14 at 06:51

Alexey B.

11,965
2
49
73

How would I be able to group the result on the client side? – mickburkejnr Jul 25 '14 at 22:57
Iterate over all results => extract month => collect it – Alexey B. Jul 26 '14 at 07:23

score 1 · Answer 2 · answered Jul 24 '14 at 22:40

1

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

answered Jul 24 '14 at 22:40

brainbowler

667
5
17

I got the following error from Symfony2 using that query: `[Syntax Error] line 0, col 56: Error: Expected known function, got 'EXTRACT'` – mickburkejnr Jul 24 '14 at 23:12
I see. The DQL apparently does not support this function. You could use a native query instead: http://doctrine-orm.readthedocs.org/en/latest/reference/native-sql.html – brainbowler Jul 24 '14 at 23:16

Olim Saidov · Answer 3 · 2014-07-31T06:48:22.863

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]]
}]

knrdk · Answer 4 · 2014-07-31T20:45:31.507

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 [..]

Group data by month and display it in a multidimensional array using Symfony2, Twig and Flot.js

4 Answers4