Ad

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')
                ->orderBy('created_at')->get();

return $transaction;

The result I get:

[{"total":"81739","date":"2015-12-22"},{"total":"82912","date":"2015-12-26"},{"total":"55912","date":"2015-12-27"},{"total":"55599","date":"2015-12-30"},{"total":"77626","date":"2015-12-31"}];

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

Thanks.

Ad

Answer

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 = d.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
Ad
source: stackoverflow.com
Ad