Show analytics of registered users for last 12 months using chart

  charts, javascript, mysql, php, symfony

I’m trying to get all users for the last 12 months from database and show some statistics using chart.For that purpose I’m using chartJS.

$registeredUsersData = $connection->fetchAll(
    COUNT(id) AS registered_users,
    CONCAT(MONTHNAME(created_on), " ", YEAR(created_on)) AS created_on
    FROM users 
    WHERE created_on 
    GROUP BY YEAR(created_on), MONTH(created_on)
    ORDER BY YEAR(created_on) DESC, MONTH(created_on) DESC;

We pass it by symfony controller and render html template.

$registeredUsersCount = array_column($registeredUsersData, 'registered_users');
$registeredUserMonths = array_column($registeredUsersData, 'created_on');

return $this->render('dashboard/admin/index.html.twig', [
    'registered_users_count' => $registeredUsersCount,
    'registered_users_months' => $registeredUserMonths

In the template I’m trying to display using a bar diagram that data where year and month should be combined and display on the abscissa(X-axis) and the count respectively on the ordinate(Y-axis).

<div class="chart-container">
    <canvas id="canvas"></canvas>

let months = {{ registered_users_months|json_encode|raw }};
let users = {{ registered_users_count|json_encode|raw }};
const ctx = document.getElementById('canvas').getContext('2d');
const myChart = new Chart(ctx, {
    type: 'bar',
    data: {
        labels: months,
        datasets: [{
            label: 'Registered Users Monthly',
            data: users,
            backgroundColor: 'rgba(90, 80, 191, 0.2)',
            borderColor: 'rgba(90, 80, 191, 1)',
            borderWidth: 1
    options: {
        maintainAspectRatio: false,
        responsive: true,
        scales: {
            yAxes: [{
                ticks: {
                    beginAtZero: true

Data is properly displayed but not all “previous” 12 months are displayed.If a month does not have registered users it must be displayed as well but with count of zero. Unfortunately, the given query does not support that function currently, how could I modify it?

Source: Symfony Questions