Laravel Get Every Date Value For Every User

- 1 answer

I try to get value for every date even there is no transaction:

$transaction = Transaction::whereBetween(DB::raw('DATE(created_at)'), ['2015-12-01', '2016-12-31'])
                ->where('vendor_id', $row)
                ->groupBy('vendor_id', DB::raw('DATE(created_at)'))
                ->selectRaw('FLOOR(SUM(amount)) as total, DATE(created_at) as date')

return $transaction;

The result I get:


How can I get value for every date if it's no value insert it with 0?.




Update: added new solution (without joining)

The idea is very simple: if you've no transactions today, today's date is not in your transaction table.

The solution is simple too: you've to store every date like so:

Run every day at 00:00:00 a cronjob that inserts an empty transaction. So you get every date in your transaction table without joining to another table.

Be care: only if there's a 'real' transaction for a date, the 'fake' (empty) transaction should be deleted.

Alternative 1:

  • Create a table with dates (with id and date), for example from 2015-01-01 to 2025-12-31
  • Select your dates and left join to the transaction table

Example query:

SELECT * FROM dates d LEFT JOIN transactions t ON t.date_id =

Instead of storing the date in your transaction table, your store the foreign key to the dates table.

Alternative 2:

  • Create a table for dates (with id and date)
  • Run every 00:00:00 a cronjob that insert the current date
  • Select your dates and left join to the transaction table

Alternative 3:

  • Generate with php's DateInterval a date range
  • Link the records from your transaction table to every right date of the range