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

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:

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.
Please or to participate in this conversation.