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


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 :


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 ??




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')"))

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')"))



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

I hope this will work for you...

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


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.