How to query the db and return a count of records for each month

You're welcome @madsynn

The query will only return results for the months that have at least one row in the database.

In order to fill in the missing data you might have to resort to array unions or collections.

You can also use a stored procedure to create a table containing all the possible dates so you're able to do a left join with your data: https://ubiq.co/database-blog/fill-missing-dates-in-mysql/

$showMonths = 8;

$startDate = \Carbon\Carbon::now()->firstOfMonth()->subMonths($showMonths - 1);

$returns = Support::query()
	->selectRaw('COUNT(*) AS total, MONTH(created_at) AS month')
	->where('case_reason_id', 1)
	->where('created_at', '>=', $startDate)
	->groupBy('month')
	->get()
	->pluck('total', 'month')
	->toArray();

$returns = array_values($returns);

Good luck!

PS: Completely untested.

@guybrush_threepwood

You rock and thank you. That seems to be working perfectly. I have one last question though. Is there a way to identify the month for each or at least the first month of record so I can label it correctly in the graph? better yet is there a way to do that and if the month has no records show 0 for that month instead of nothing?

Here are the outputs:

Returns >

array:4 [▼
  0 => 9
  1 => 40
  2 => 29
  3 => 5
]

Troubleshooting

array:3 [▼
  0 => 4
  1 => 5
  2 => 3
]

As you can see am not sure what each month is and not sure what one skipped a month.

Thanks for you awesome help.

Query between dates and group by, and if a month doesn't have results, let it show 0, which a group by would.

Just a suggestion, but have a look at: https://www.mysqltutorial.org/mysql-group-by.aspx/

They have some good examples.

No problem @madsynn, glad I could help!

The original query returns the month number as the array keys, so just be deleting this line:

$returns = array_values($returns);

Should give you an array like this (where the keys are the month numbers):

array:4 [▼
  7 => 9
  8 => 40
  9 => 29
  10 => 5
]

So to get the month numbers you can:

array_keys($returns);

And to get the values:

array_values($returns);

Let me get back to you with the rest of the question (filling in the missing values).

@guybrush_threepwood

I will watch for your message on the rest. You Rock.

Hey @madsynn

This should work, but it gets a little wordy. Maybe you could extract the reusable logic to a helper function:

$showMonths = 8;

$startDate = \Carbon\Carbon::now()->firstOfMonth()->subMonths($showMonths - 1);
$endDate = \Carbon\Carbon::now()->lastOfMonth()->endOfDay();
$period = Carbon\CarbonPeriod::create($startDate, '1 month', $endDate);

$returns = Support::query()
	->selectRaw('COUNT(*) AS total, DATE_FORMAT(created_at, "%Y-%m-01") AS month')
	->where('case_reason_id', 1)
	->where('created_at', '>=', $startDate)
    ->where('created_at', '<=', $endDate)
    ->orderBy('created_at', 'asc')
	->groupBy('month')
	->get()
	->pluck('total', 'month')
	->toArray();

foreach($period as $date) {
  	$dateString = $date->toDateString();
  	if (! array_key_exists($dateString, $returns)) {
        $returns[$dateString] = 0;
    }
}

$values = array_values($returns);

$labels = array_keys($returns);

array_walk($labels, function(&$value) {
    $value = \Carbon\Carbon::parse($value)->shortMonthName;
});

I couldn't test the query for obvious reasons, but here's a working snippet with a test array: https://web.tinkerwell.app/#/snippets/04144608-f8a2-4b90-8f1a-5d0626e1ddc5

PS: It would be much easier and concise if you created a dates table and populated it with all the possible dates (first days of each month for the forseeable future).

You could just solve it with a simple subquery. No more array functions, carbon date ranges, etc.

$showMonths = 8;

$startDate = \Carbon\Carbon::now()->firstOfMonth()->subMonths($showMonths - 1);
$endDate = \Carbon\Carbon::now()->lastOfMonth()->endOfDay();

$supports = DB::table('supports')
	->selectRaw('COUNT(*) AS total, DATE_FORMAT(created_at, "%Y-%m-01") AS month')
	->where('case_reason_id', 1)
	->groupBy('month');

$returns = DB::table('dates')
    ->leftJoinSub($supports, 'supports_grouped', function ($join) {
        $join->on('dates.date', '=', 'supports_grouped.month');
    })
	->selectRaw('date, DATE_FORMAT(date, "%b") AS month_name, total')
   	->where('date', '>=', $startDate)
    ->where('date', '<=', $endDate)
    ->orderBy('date', 'asc')
    ->get()
	->pluck('total', 'month_name')
	->toArray();

Sample migration to create the table and execute a stored procedure to populate it:

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateDatesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('dates', function (Blueprint $table) {
            $table->increments('id');
            $table->date('date')->index();
        });

        DB::unprepared('DROP PROCEDURE IF EXISTS fill_dates');

        DB::unprepared('
            CREATE PROCEDURE fill_dates(start_date DATE, end_date DATE)
            BEGIN
              DECLARE crt_date DATE;
              SET crt_date = start_date;
              WHILE crt_date <= end_date DO
                INSERT INTO dates(date) VALUES(crt_date);
                SET crt_date = ADDDATE(crt_date, INTERVAL 1 MONTH);
              END WHILE;
            END
        ');

        DB::unprepared('CALL fill_dates("2021-01-01", "2031-01-01")');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        //
    }
}

Lastly, this is how you can extract the logic to a little helper if you decide to go with option "A":

protected function prepareData(\Carbon\Carbon $startDate, \Carbon\Carbon $endDate, array $data)
{
    $period = Carbon\CarbonPeriod::create($startDate, '1 month', $endDate);

    foreach($period as $date) {

        $dateString = $date->toDateString();

        if (! array_key_exists($dateString, $data)) {
            $data[$dateString] = 0;
        }
    }

    $values = array_values($data);

    $labels = array_keys($data);

    array_walk($labels, function(&$value) {
        $value = \Carbon\Carbon::parse($value)->shortMonthName;
    });

    return [$values, $labels];
}

$showMonths = 8;

$startDate = \Carbon\Carbon::now()->firstOfMonth()->subMonths($showMonths - 1);
$endDate = \Carbon\Carbon::now()->lastOfMonth()->endOfDay();

// Replace with query
$returns = [
    '2021-07-01' => 200,
    '2021-08-01' => 400,
    '2021-10-01' => 1000,
];

$returns = $this->prepareData($startDate, $endDate, $returns);