How to get monthly reports in laravel and even if no record

Question

I have to make a report for a table in MySQL using Laravel. I have an incomes table like

ID amount created_at
1  200    2020-09-30
2  900    2021-03-10

So if I fill start_date and end_date to fillter is 2020-08-25 and 2021-03-10. The result I expect is

amount  month   year
0       08      2020
200     09      2020
0       10      2020   
0       11      2020   
0       12      2020 
0       01      2021 
0       02      2021
900     03      2021

Can you help me about this issue? MySQL 8 and laravel 8.

I'm trying to get the report

$income = DriverIncome::where('driver_id', Auth::user()->id)->whereBetween('created_at', [$startDate, $endDate]);

$incomeExists = $income->selectRaw('SUM(amount) as amount, MONTH(created_at) as name, YEAR(created_at) as year')->groupBy('name', 'year')->orderBy('name', 'asc')->get();

And the result is

amount  month   year

200     09      2020
900     03      2021

Answer 1

This can be solved by:

Steps:

1. Creating a temporary table (tmp_dates).
2. Creating a stored procedure to allow filling dates in the temporary table.
3. Populating the temporary table with all dates within the range (dateStart - dateEnd).
4. Joining the temporary table (tmp_dates) with the incomes table (Where tmp_dates.date column = incomes.created_at column).
5. Deleting the temporary table.

Sample Data:

CREATE TABLE `drivers` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ssn` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `drivers` (`id`, `name`, `ssn`, `created_at`, `updated_at`) VALUES
(1, 'John', '456', NULL, NULL),
(2, 'Peter', '13', NULL, NULL);

ALTER TABLE `drivers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `drivers_ssn_unique` (`ssn`);

ALTER TABLE `drivers`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

CREATE TABLE `incomes` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `driver_id` bigint(20) UNSIGNED NOT NULL,
  `amount` decimal(8,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `incomes` (`id`, `driver_id`, `amount`, `created_at`, `updated_at`) VALUES
(1, 1, '456.00', '2021-01-01 00:56:00', NULL),
(2, 1, '46.00', '2021-01-01 23:56:00', NULL),
(3, 1, '6.00', '2021-02-28 22:56:00', NULL),
(4, 1, '40.00', '2021-03-01 02:46:00', NULL),
(5, 1, '34.20', '2021-05-01 00:56:00', NULL),
(6, 1, '6.89', '2021-05-02 04:56:00', NULL),
(7, 1, '5.00', '2020-05-01 00:56:00', NULL),
(8, 1, '9.00', '2020-08-01 04:56:00', NULL),
(9, 1, '3.90', '2020-08-06 00:56:00', NULL),
(10, 1, '3.70', '2020-10-01 00:56:00', NULL),
(11, 2, '456.00', '2021-07-01 00:56:00', NULL),
(12, 2, '46.00', '2021-07-01 23:56:00', NULL),
(13, 2, '6.90', '2021-02-28 22:56:00', NULL),
(14, 2, '45.00', '2021-09-01 02:46:00', NULL),
(15, 2, '134.20', '2021-09-01 00:56:00', NULL),
(16, 2, '56.89', '2021-10-02 04:56:00', NULL),
(17, 2, '5.00', '2020-03-01 00:56:00', NULL),
(18, 2, '19.00', '2020-05-01 04:56:00', NULL),
(19, 2, '13.90', '2020-05-06 00:56:00', NULL),
(20, 2, '66.70', '2020-11-01 00:56:00', NULL);

ALTER TABLE `incomes`
  ADD PRIMARY KEY (`id`),
  ADD KEY `incomes_driver_id_foreign` (`driver_id`);

ALTER TABLE `incomes`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;

ALTER TABLE `incomes`
  ADD CONSTRAINT `incomes_driver_id_foreign` FOREIGN KEY (`driver_id`) REFERENCES `drivers` (`id`) ON DELETE CASCADE;

MySQL Solution:

-- 1. Creating a temporary table (tmp_dates).

DROP TEMPORARY TABLE IF EXISTS tmp_dates;

CREATE
TEMPORARY TABLE tmp_dates(
   date DATE,
    UNIQUE(date)
);

-- 2. Creating a stored procedure to allow filling dates in the temporary table.

DROP PROCEDURE IF EXISTS fillDates;
DELIMITER |
CREATE PROCEDURE fillDates(dateStart DATE, dateEnd DATE)
BEGIN
  WHILE
dateStart <= dateEnd DO
    INSERT INTO tmp_dates (date) VALUES (dateStart);
    SET
dateStart = date_add(dateStart, INTERVAL 1 DAY);
END WHILE;
END;
|
DELIMITER ;

-- 3. Populating the temporary table with all dates within the range (dateStart - dateEnd).

CALL fillDates('2020-10-01','2021-05-01');

-- 4. Joining the temporary table (tmp_dates) with the 'incomes' table (Where tmp_dates.date column = incomes.created_at column).

SELECT IFNULL(SUM(amount), 0) as amount,
       DATE_FORMAT(tmp_dates.date, '%m') AS month,
    YEAR(tmp_dates.date) AS year
FROM
    incomes
    RIGHT JOIN tmp_dates
ON DATE(incomes.created_at) = tmp_dates.date
WHERE driver_id = 1 OR id IS NULL
GROUP BY
    YEAR ( tmp_dates.date ),
    MONTH ( tmp_dates.date )
ORDER BY
    YEAR ( tmp_dates.date ),
    MONTH ( tmp_dates.date );

-- 5. Deleting the temporary table.

DROP TEMPORARY TABLE IF EXISTS tmp_dates;

MySQL Output:

+--------+-------+------+
| amount | month | year |
+--------+-------+------+
|   3.70 | 10    | 2020 |
|   0.00 | 11    | 2020 |
|   0.00 | 12    | 2020 |
| 502.00 | 01    | 2021 |
|   0.00 | 02    | 2021 |
|  46.00 | 03    | 2021 |
|   0.00 | 04    | 2021 |
|  34.20 | 05    | 2021 |
+--------+-------+------+

Laravel Solution:

A. Create the stored procedure.

A1). php artisan make:migration create_fill_dates_stored_procedure

A2). In the create_fill_dates_stored_procedure migration file...

// ...
    public function up()
    {
        DB::statement('DROP PROCEDURE IF EXISTS fillDates');

        DB::statement('
            CREATE PROCEDURE fillDates(dateStart DATE, dateEnd DATE)
            BEGIN
              WHILE
            dateStart <= dateEnd DO
                INSERT INTO tmp_dates (date) VALUES (dateStart);
                SET
            dateStart = date_add(dateStart, INTERVAL 1 DAY);
            END WHILE;
            END;
        ');
    }

    public function down()
    {
        DB::statement('DROP PROCEDURE IF EXISTS fillDates');
    }
// ...

A3). php artisan migrate

B. Create the Income & Driver models and migrations.

B1). php artisan make:model Driver -m

B2). php artisan make:model Income -m

B3). In the create_drivers_table.php migration file...

// ...
    public function up()
    {
        Schema::create('drivers', function (Blueprint $table) {
            $table->id();
            $table->string("name");
            $table->string("ssn")->nullable()->unique();
            $table->timestamps();
        });
    }
// ...

B4). In the create_incomes_table.php migration file...

// ...
    public function up()
    {
        Schema::create("incomes", function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger("driver_id");
            $table->foreign("driver_id")->references("id")->on("drivers")->onDelete("cascade");
            $table->decimal("amount");
            $table->timestamps();
        });
    }
// ...

B5). php artisan migrate

C. (Laravel Solution). In your Income model...

// ...
    public static function monthlyReport(int $driverId, string $dateStart, string $dateEnd)
    {

        config()->set('database.connections.mysql.strict', false);
        DB::reconnect(); // Important as the existing connection if any would be in strict mode.

        DB::statement('DROP TEMPORARY TABLE IF EXISTS tmp_dates');

        DB::statement('
            CREATE
            TEMPORARY TABLE tmp_dates(
               date DATE,
                UNIQUE(date)
            );
        ');

        DB::statement('CALL fillDates(?,?);', [$dateStart, $dateEnd]);

        $result = \App\Models\Income::query()
            ->rightJoin("tmp_dates", function ($join) {
                $join->on(DB::raw("DATE(incomes.created_at)"), "=", "tmp_dates.date");
            })
            ->where(function ($query) use ($driverId) {
                return $query->where("driver_id", $driverId)
                    ->orWhereNull("id");
            })
            ->selectRaw("IFNULL(SUM(amount), 0) as amount, DATE_FORMAT(tmp_dates.date,'%m') AS month, YEAR(tmp_dates.date) AS year")
            ->groupByRaw("YEAR(tmp_dates.date), MONTH(tmp_dates.date)")
            ->orderByRaw("YEAR(tmp_dates.date), MONTH(tmp_dates.date)")
            ->get();

        DB::statement('DROP TEMPORARY TABLE IF EXISTS tmp_dates;');

        // Now changing back the strict ON.
        config()->set('database.connections.mysql.strict', true);
        DB::reconnect();

        return $result;
    }
// ...

Laravel Output:

Ivan@DESKTOP-LQIK691 MINGW64 /c/xampp/htdocs/breezep (master)
$ php artisan tinker
Psy Shell v0.10.6 (PHP 8.0.2 — cli) by Justin Hileman
>>> App\Models\Income::monthlyReport(1, "2020-10-01", "2021-05-01")
=> Illuminate\Database\Eloquent\Collection {#4348
     all: [
       App\Models\Income {#4349
         amount: "3.70",
         month: "10",
         year: 2020,
       },
       App\Models\Income {#4350
         amount: "0.00",
         month: "11",
         year: 2020,
       },
       App\Models\Income {#4351
         amount: "0.00",
         month: "12",
         year: 2020,
       },
       App\Models\Income {#4352
         amount: "502.00",
         month: "01",
         year: 2021,
       },
       App\Models\Income {#4353
         amount: "0.00",
         month: "02",
         year: 2021,
       },
       App\Models\Income {#4354
         amount: "46.00",
         month: "03",
         year: 2021,
       },
       App\Models\Income {#4355
         amount: "0.00",
         month: "04",
         year: 2021,
       },
       App\Models\Income {#4356
         amount: "34.20",
         month: "05",
         year: 2021,
       },
     ],
   }
>>>

Answer 2

GROUP BY can do what you want:

SELECT
    SUM(amount) as amount,
    YEAR ( created_at ) AS YEAR,
    MONTH ( created_at ) AS MONTH 
FROM
    incomes
GROUP BY
    YEAR ( created_at ),
    MONTH ( created_at )