Laravel: Sum daily (groupby date) then retain date with empty rows

Question

stackoverflow,

I have working laravel function which get the daily sum of sales for the last 30 days. I will use the data to build a graph, so I need to get the dates even if its empty and give them a value of "0" as their sum.

here's my code (it's working but only returns dates which are not empty)

public function getDaily() {
    $startDate = Carbon::now()->subDays(30);
    $endDate = Carbon::now();
    $all_dates = array();

   for($i = 0;$i<=30;$i++)
   {
        $all_dates[] = $startDate->toDateString();
        $startDate->addDay();
        $sales=DB::table('sale_details')
        ->select(DB::raw('sum(amount_due) as daily'),DB::raw('date(created_at) as date'))
        ->groupBy('date')
        ->orderBy('date','desc')
       ->get();
   }
    return $sales;
}

zlodes · Accepted Answer · 2020-05-05T13:01:05.077

2

To get array of objects you may use good Collection's methods:

$sales = DB::table('sale_details')
    ->whereBetween('created_at', [$startDate, $endDate])
    ->select([
        DB::raw('sum(amount_due) as daily'),
        DB::raw('date(created_at) as date'),
    ])
    ->groupBy('date')
    ->get()
    ->keyBy('date');

$period = new CarbonPeriod($startDate, '1 day', $endDate);

// Fill zeroes
foreach ($period as $date) {
    $dateString = $date->toDateString();

    if (!$sales->has($dateString)) {
        $sales->put($dateString, ['date' => $dateString, 'daily' => 0]);
    }
}

// Convert to associative array
$sales = $sales->values()->toArray();

edited May 05 '20 at 13:01

answered May 05 '20 at 12:41

zlodes

784
5
16

Hi @zlodes I am getting this error: "ErrorException Undefined index: 2020-04-05" I am trying to resolve it, sorry I am just learning laravel and js during the quarantine... – regit May 05 '20 at 12:54
Good! Don't stop! :) – zlodes May 05 '20 at 13:10
Hello again zlodes, need you help again on this part, Is there a way to sort date dates form that function? since the first part of the query only get the dates with values, and on the second part is will 'put' all the days with 0 values resulting to random dates – regit May 07 '20 at 15:55
Hi! You may use `sort` method from Laravel Collection and last line of code will look like this: `$sales = $sales->values()->sortBy('date')->toArray();` If you need to sort by desk, you may use method `sortByDesk` instead of `sortBy`. Docs reference: https://laravel.com/docs/7.x/collections#method-sort – zlodes May 07 '20 at 17:37
Already tried that and is not working :( Will try to figure out this myself. Thanks! appreciated all your help! – regit May 08 '20 at 04:24
just to update this, sortBy is working when I'm testing with dd('$sales'); but when returned to ajax request the dates are not sorted anymore. – regit May 08 '20 at 05:53

score 1 · Answer 2 · answered May 05 '20 at 12:04

1

Try this:

$sales = DB::table('sale_details')
    ->whereBetween('created_at', [$startDate, $endDate])
    ->select([
        DB::raw('sum(amount_due) as daily'),
        DB::raw('date(created_at) as date'),
    ])
    ->groupBy('date')
    ->orderBy('date','desc')
    ->pluck('daily', 'date')
    ->toArray();

$period = new CarbonPeriod($startDate, '1 day', $endDate);

// Fill zeroes
foreach ($period as $date) {
    if (!isset($sales[$date->toDateString()])) {
        $sales[$date->toDateString()] = 0;
    }
}

Another solution is using database-generated series of dates (pgsql example: Generating time series between two dates in PostgreSQL) and join it with result.

answered May 05 '20 at 12:04

zlodes

784
5
16

wow! thanks, perfectly working. I am getting this kind of result: { "2020-05-05": 3307, "2020-04-28": 12.2, "2020-04-26": 460.47, "2020-04-05": 0, "2020-04-06": 0, "2020-04-07": 0, "2020-04-12": 0, ........ } can you help me again on how to access that kind of result as an array? I will use them in chart, so I need to get the dates as set of array1 and the sum value as set of array2. in ajax or js – regit May 05 '20 at 12:23
@regit posted new answer bellow. – zlodes May 05 '20 at 12:42

Laravel: Sum daily (groupby date) then retain date with empty rows

2 Answers2