php - how to get the count of the posts for each month in laravel

452

i want to make charts in my laravel application and i want to show how many posts was created in each month so i need a sort of api like below :

{
count:22,
month:1-1-2020
},
{
count:18,
month:1-2-2020
}

here is what i tried to do :

       return Post::all()->groupBy(function($post) { // Get all posts as collection and apply groupBy method
            $post->created_at->format('F'); // ex: September
        });

but it didint work to group the counts by the month so is there any way to achive this ??

876

Answer

Solution:

You may try aggregating byDATE_FORMAT of your date, with the mask%Y-%m:

$results = Post::select(\DB::raw("DATE_FORMAT(created_at, '%Y-%m') AS ym, COUNT(*) AS cnt"))
->groupBy(\DB::raw("DATE_FORMAT(created_at, '%Y-%m')"))
->get();

If your actual database be Postgres, then a similar approach usingTO_CHAR should work:

$results = Post::select(\DB::raw("TO_CHAR(created_at, 'YYYY-MM') AS ym, COUNT(*) AS cnt"))
->groupBy(\DB::raw("TO_CHAR(created_at, 'YYYY-MM')"))
->get();
951

Answer

Solution:

here you go

return Post::select(DB::raw('count(1) AS count'), DB::raw('DATE_FORMAT(created_at, "01-%m-%Y") AS month'))->groupBy(function($post) { // Get all posts as collection and apply groupBy method
     $post->created_at->format('01-m-Y'); // ex: 01-02-2019
})->get()

I hope this will work for you...

People are also looking for solutions to the problem: ajax passing parameter to php file not working

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.