按月对数据进行分组，并使用Symfony2，Twig和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?

Answer 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