Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

lat4732's avatar
Level 12

Need some help with a query and a chart

Hey everyone!

I have a graph that is separated by months for a period of 1 year. I'm displaying how many reviews a website received each month. Here's my query:

$website->loadCount([
    'reviews as firstMonth' => function ($query) {
        $query->whereBetween('created_at', [Carbon::now()->subMonths(12), Carbon::now()->subMonths(11)]);
    },
    'reviews as secondMonth' => function ($query) {
        $query->whereBetween('created_at', [Carbon::now()->subMonths(11), Carbon::now()->subMonths(10)]);
    },
    'reviews as thirdMonth' => function ($query) {
        $query->whereBetween('created_at', [Carbon::now()->subMonths(10), Carbon::now()->subMonths(9)]);
    },
    'reviews as fourthMonth' => function ($query) {
        $query->whereBetween('created_at', [Carbon::now()->subMonths(9), Carbon::now()->subMonths(8)]);
    },
    'reviews as fifthMonth' => function ($query) {
        $query->whereBetween('created_at', [Carbon::now()->subMonths(8), Carbon::now()->subMonths(7)]);
    },
    'reviews as sixthMonth' => function ($query) {
        $query->whereBetween('created_at', [Carbon::now()->subMonths(7), Carbon::now()->subMonths(6)]);
    },
    'reviews as seventhMonth' => function ($query) {
        $query->whereBetween('created_at', [Carbon::now()->subMonths(6), Carbon::now()->subMonths(5)]);
    },
    'reviews as eighthMonth' => function ($query) {
        $query->whereBetween('created_at', [Carbon::now()->subMonths(5), Carbon::now()->subMonths(4)]);
    },
    'reviews as ninthMonth' => function ($query) {
        $query->whereBetween('created_at', [Carbon::now()->subMonths(4), Carbon::now()->subMonths(3)]);
    },
    'reviews as tenthMonth' => function ($query) {
        $query->whereBetween('created_at', [Carbon::now()->subMonths(3), Carbon::now()->subMonths(2)]);
    },
    'reviews as eleventhMonth' => function ($query) {
        $query->whereBetween('created_at', [Carbon::now()->subMonths(2), Carbon::now()->subMonths(1)]);
    },
    'reviews as twelfthMonth' => function ($query) {
        $query->whereBetween('created_at', [Carbon::now()->subMonths(1), Carbon::now()]);
    },
])

And here's how my graph is looking

visualization

But I need something more detailed. Something like a distribution of the reviews. I need to separate these reviews received by stars. In short I need to display what reviews were received on each month. Here's an example:

vizualization

And here is the first problem. I started writing the query to get what reviews were received for each month but I realized that's not the right way of doing it:

'reviews as firstMonth5Star' => function ($query) {
    $query->whereBetween('created_at', [Carbon::now()->subMonths(12), Carbon::now()->subMonths(11)])
          ->where('review_stars', 5);
},
'reviews as firstMonth4Star' => function ($query) {
    $query->whereBetween('created_at', [Carbon::now()->subMonths(12), Carbon::now()->subMonths(11)5])
          ->where('review_stars', 4);
},
// ...
'reviews as secondMonth5Star' => function ($query) {
    $query->whereBetween('created_at', [Carbon::now()->subMonths(11), Carbon::now()->subMonths(10)])
          ->where('review_stars', 5);
},
'reviews as secondMonth4Star' => function ($query) {
    $query->whereBetween('created_at', [Carbon::now()->subMonths(11), Carbon::now()->subMonths(10)])
          ->where('review_stars', 4);
},
// ...

doing it this way is painful. How can I make the query shorter? And here's the second problem. That's how my chart is being displayed (I'm using Chart.js 3.7.0):

var xValues = [
    '{{ $graph->firstMonth }}', 
    '{{ $graph->secondMonth }}', 
    '{{ $graph->thirdMonth }}', 
    '{{ $graph->fourthMonth }}', 
    '{{ $graph->fifthMonth }}', 
    '{{ $graph->sixthMonth }}', 
    '{{ $graph->seventhMonth }}', 
    '{{ $graph->eighthMonth }}', 
    '{{ $graph->ninthMonth }}', 
    '{{ $graph->tenthMonth }}', 
    '{{ $graph->eleventhMonth }}', 
    '{{ $graph->twelfthMonth }}'];

const data = {
    labels: [
        '{{ now()->subMonths(12)->format("F, Y") }}',
        '{{ now()->subMonths(11)->format("F, Y") }}',
        '{{ now()->subMonths(10)->format("F, Y") }}',
        '{{ now()->subMonths(9)->format("F, Y") }}',
        '{{ now()->subMonths(8)->format("F, Y") }}',
        '{{ now()->subMonths(7)->format("F, Y") }}',
        '{{ now()->subMonths(6)->format("F, Y") }}',
        '{{ now()->subMonths(5)->format("F, Y") }}',
        '{{ now()->subMonths(4)->format("F, Y") }}',
        '{{ now()->subMonths(3)->format("F, Y") }}',
        '{{ now()->subMonths(2)->format("F, Y") }}',
        '{{ now()->subMonths(1)->format("F, Y") }}',
    ],
    datasets: [{
        data: xValues,
        backgroundColor: [
        'rgba(255, 99, 132, 0.2)',
        'rgba(255, 159, 64, 0.2)',
        'rgba(255, 205, 86, 0.2)',
        'rgba(75, 192, 192, 0.2)',
        'rgba(54, 162, 235, 0.2)',
        'rgba(153, 102, 255, 0.2)',
        'rgba(201, 203, 207, 0.2)'
        ],
        borderColor: [
        'rgb(255, 99, 132)',
        'rgb(255, 159, 64)',
        'rgb(255, 205, 86)',
        'rgb(75, 192, 192)',
        'rgb(54, 162, 235)',
        'rgb(153, 102, 255)',
        'rgb(201, 203, 207)'
        ],
        borderWidth: 1,
    }]
};

new Chart("reviewsChart", {
    type: 'bar',
    data: data,
    options: {
        scales: {
            y: {
                beginAtZero: true
            }
        },
        plugins: {
            legend: {
                display: false,
            },
        },
        ticks: {
            precision: 0
        }
    },
});

But how can I add additional label information? That's my first time dealing with chart.js.

0 likes
0 replies

Please or to participate in this conversation.