How do i get count of records for each month in an array

I have a clients table where I am trying to get data count for each months (using the created_at column). The selected year variable below ($selectedYear) is coming from a form request to select which year to display.

public function clientsCount(Request $request)
{
    $selectedYear = $request->selectedYear ?? now()->format('Y');

    $orderedMonthsData = [
        1 => "January ". $selectedYear,
        2 => "February ". $selectedYear,
        3 => "March ". $selectedYear,
        4 => "April ". $selectedYear,
        5 => "May ". $selectedYear,
        6 => "June ". $selectedYear,
        7 => "July ". $selectedYear,
        8 => "August ". $selectedYear,
        9 => "September ". $selectedYear,
        10 => "October ". $selectedYear,
        11 => "November ". $selectedYear,
        12 => "December ". $selectedYear
    ];

    $clientYear = Client::query()->whereYear('created_at', $selectedYear);

    $orderedClientsData = [
        1 => $clientYear->whereMonth('created_at', 1)->count(),
        2 => $clientYear->whereMonth('created_at', 2)->count(),
        3 => $clientYear->whereMonth('created_at', 3)->count(),
        4 => $clientYear->whereMonth('created_at', 4)->count(),
        5 => $clientYear->whereMonth('created_at', 5)->count(),
        6 => $clientYear->whereMonth('created_at', 6)->count(),
        7 => $clientYear->whereMonth('created_at', 7)->count(),
        8 => $clientYear->whereMonth('created_at', 8)->count(),
        9 => $clientYear->whereMonth('created_at', 9)->count(),
        10 => $clientYear->whereMonth('created_at', 10)->count(),
        11 => $clientYear->whereMonth('created_at', 11)->count(),
        12 => $clientYear->whereMonth('created_at', 12)->count(),
    ];

    dd($orderedMonthsData, $orderedClientsData);
}

when i die dump it, this is the result.

How do i get count of records for each month in an array

It worked perfectly for the first array ($orderedMonthsData) but the second array ($orderedClientsData) is not working perfectly. I have records in other months which means their count is not supposed to be 0. In the second result, It means i am only getting result for the first item in the array. how can i query it so that i will get the count for other months too?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Retrieve data only once from the database

public function clientsCount(Request $request)
{
    $selectedYear = $request->selectedYear ?? now()->format('Y');

    $data = Client::selectRaw('count(id) as count, month(created_at) as month')
        ->whereYear('created_at', $selectedYear)
        ->groupBy('month')
        ->pluck('count', 'month');

    $period = CarbonPeriod::create(
        now()->setYear($selectedYear)->startOfYear(), '1 month', now()->setYear($selectedYear)->endOfYear()
    );

    $orderedMonthsData = [];

    foreach ($period as $key => $date) {
        $orderedMonthsData[++$key] = $date->format('F Y');
    }

    $orderedClientsData = [];

    foreach ($orderedMonthsData as $month => $text) {
        $orderedClientsData[$month] = $data->get($month) ?? 0;
    }

    // dd($orderedMonthsData, $orderedClientsData);
}

You don’t have to create orderedMonthsData manually. You can make it with a code what I provided above for you.

Method 2

[This is NOT a solution, but I need some space to answer you.]
I don’t think anybody can say why all your function calls return 0, except the first.

For example:

$clientYear->whereMonth('created_at', 2)

appears to return 0 records, but you claim it should return a higher number.

My advise would be to first use normal SQL without the frameworking and see how many records return when querying the right table, using the year 2019 and the month 2.

Work your way up from there through eloquent en laravel.

Method 3

try this One

public function clientsCount(Request $request)
    {
        $selectedYear = $request->selectedYear ?? now()->format('Y');

        $clientsJoinedInYear = Client::query()
            ->whereYear('created_at', $selectedYear)
            ->orderBy('created_at')
            ->selectRaw('DATE_FORMAT(created_at, "%M %Y") as date_of_joining_month_and_year ,count(*) as no_of_joined_clients')
            ->groupBy('date_of_joining_month_and_year')
            ->pluck('no_of_joined_clients', 'date_of_joining_month_and_year');

        dd($clientsJoinedInYear);
    }

This will give you the exact result that you are looking for.

And DON’T EVER PASS ANY INPUT FROM END USER INTO SELECTRAW
FUNCTION


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x