CODEWITHSUNDEEP
laravel
regit
regit

Reputation: 17

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

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;
}

Upvotes: 0

Views: 989

Answers (2)

zlodes
zlodes

Reputation: 784

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();

Upvotes: 2

zlodes
zlodes

Reputation: 784

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.

Upvotes: 1

Related Questions