I need some help with a count query

I need to count reviews for the past 12 months grouped by months. First thing that appear in my head is to assigning values to 12 different variables. The second thing appeared in my head is that I can use loadCount for it:

->loadCount([
'reviews as firstMonth' => function ($query) {
    $query->whereBetween('created_at', [Carbon::now()->subMonths(11), Carbon::now()]);
},
'reviews as secondMonth' => function ($query) {
    $query->whereBetween('created_at', [Carbon::now()->subMonths(10), Carbon::now()]);
},
'reviews as thirdMonth' => function ($query) {
    $query->whereBetween('created_at', [Carbon::now()->subMonths(9), Carbon::now()]);
},
....

What do you think about it? How should I do it?

I'll use chart.js for the graph. Any suggestions how to integrate it into this JS code?

var xValues = [50,60,70,80,90,100,110,120,130,140,150];
var yValues = [7,8,8,9,9,9,10,11,14,14,15];

new Chart("reviewsChart", {
        type: "line",
        data: {
            labels: xValues,
            datasets: [{
            backgroundColor: "rgba(0,0,0,1.0)",
            borderColor: "rgba(0,0,0,0.1)",
            data: yValues
            }]
        }
});

lat4732

4 years ago

Level 12

Hey!

I ended up with this

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

but its not working properly. I'm getting as results:

    "firstMonth" => 2
    "secondMonth" => 2
    "thirdMonth" => 2
    "fourthMonth" => 2
    "fifthMonth" => 2
    "sixthMonth" => 2
    "seventhMonth" => 2
    "eighthMonth" => 2
    "ninthMonth" => 2
    "tenthMonth" => 2
    "eleventhMonth" => 2
    "twelfthMonth" => 2

which isn't correct. This website have total of 2 reviews and they are written literally now (for test purposes). Any idea what's wrong?

Sinnbeck

4 years ago

Best Answer

Level 102

@Laralex You are always checking up to now.. So every scope with have those two reviews..

I assume you want only 1 month

'reviews as firstMonth' => function ($query) {
        $query->whereBetween('created_at', [Carbon::now()->subMonths(12), Carbon::now()->subMonths(11)]);
    },

Please or to participate in this conversation.