<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
public function up(): void
{
Schema::create('daily_sales_summaries', function (Blueprint $table) {
$table->id();
$table->date('summary_date')->unique();
$table->unsignedInteger('orders_count')->default(0);
$table->decimal('gross_total', 12, 2)->default(0);
$table->decimal('refunds_total', 12, 2)->default(0);
$table->timestamps();
});
}
public function down(): void
{
Schema::dropIfExists('daily_sales_summaries');
}
};
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
class DailySalesSummary extends Model
{
protected $fillable = [
'summary_date',
'orders_count',
'gross_total',
'refunds_total',
];
protected $casts = [
'summary_date' => 'date',
'gross_total' => 'decimal:2',
'refunds_total' => 'decimal:2',
];
public function netRevenue(): string
{
return bcsub($this->gross_total, $this->refunds_total, 2);
}
public function scopeForRange(Builder $query, $start, $end): Builder
{
return $query->whereBetween('summary_date', [$start, $end])
->orderBy('summary_date');
}
}
<?php
namespace App\Console\Commands;
use App\Models\DailySalesSummary;
use Illuminate\Console\Command;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
class AggregateDailySales extends Command
{
protected $signature = 'sales:aggregate-daily {--date= : Day to summarize (Y-m-d), defaults to yesterday}';
protected $description = 'Roll up paid orders into a daily sales summary row';
public function handle(): int
{
$day = $this->option('date')
? Carbon::parse($this->option('date'))->startOfDay()
: Carbon::yesterday()->startOfDay();
$end = $day->copy()->addDay();
$totals = DB::table('orders')
->selectRaw('COUNT(*) as orders_count')
->selectRaw('COALESCE(SUM(total_amount), 0) as gross_total')
->selectRaw('COALESCE(SUM(refunded_amount), 0) as refunds_total')
->where('status', 'paid')
->where('created_at', '>=', $day)
->where('created_at', '<', $end)
->first();
$summary = DailySalesSummary::updateOrCreate(
['summary_date' => $day->toDateString()],
[
'orders_count' => (int) $totals->orders_count,
'gross_total' => $totals->gross_total,
'refunds_total' => $totals->refunds_total,
]
);
$this->info(sprintf(
'Summarized %s: %d orders, net %s',
$day->toDateString(),
$summary->orders_count,
$summary->netRevenue()
));
return self::SUCCESS;
}
}
<?php
namespace App\Console;
use App\Console\Commands\AggregateDailySales;
use Illuminate\Console\Scheduling\Schedule;
use Illuminate\Foundation\Console\Kernel as ConsoleKernel;
class Kernel extends ConsoleKernel
{
protected function schedule(Schedule $schedule): void
{
$schedule->command('sales:aggregate-daily')
->dailyAt('01:15')
->withoutOverlapping()
->onOneServer()
->runInBackground();
}
protected function commands(): void
{
$this->load(__DIR__ . '/Commands');
require base_path('routes/console.php');
}
}
This snippet shows how a recurring reporting rollup is built in Laravel: raw order rows are collapsed into one summary row per day so dashboards read from a small, indexed table instead of scanning the full orders table on every request. The pattern is a classic pre-aggregation, trading a little write-time work and some data staleness for cheap, predictable reads.
The create_daily_sales_summaries_table migration defines the target table. Each row is keyed by summary_date with a unique constraint, which is the linchpin of the whole design: it lets the command re-run for the same day without creating duplicates, using an idempotent upsert. Money is stored as decimal to avoid float rounding, and orders_count plus refunds_total give enough detail for a basic revenue view.
The DailySalesSummary model is a thin Eloquent model. summary_date is cast to a date and the netRevenue() accessor derives net figures on read rather than storing a redundant column that could drift out of sync. The forRange() scope keeps reporting queries expressive and reusable.
The heart of the feature is AggregateDailySales command. Its handle() method resolves a target day from the optional --date argument, defaulting to yesterday so a nightly run summarizes a complete day. It builds a single grouped aggregate query with selectRaw over the orders table, computing SUM and COUNT in the database — far cheaper than pulling rows into PHP. The result is written with updateOrCreate, matched on summary_date, so running the command twice simply overwrites the day's totals; this makes backfills and retries safe.
Because the query only touches paid orders within a half-open [start, end) interval, it avoids double-counting boundary timestamps and naturally ignores pending or cancelled orders. The --date argument also enables backfilling historical days in a loop from a shell.
The Console schedule registration wires the command into Laravel's scheduler with dailyAt('01:15') and withoutOverlapping(), so a long-running rollup never stacks on top of itself. Reaching for this approach makes sense once report queries grow slow; the main trade-offs are eventual consistency and the need to re-aggregate when historical orders change.
Related snips
<?php
namespace App\Providers;
use App\Contracts\PaymentGateway;
use App\Services\StripePaymentGateway;Laravel service container and dependency injection
{
"private": true,
"scripts": {
"dev": "vite",
"build": "vite build"
},Laravel mix/Vite for asset compilation
class ReportQuery
SQL = <<~SQL.freeze
SELECT date_trunc('day', events.created_at) AS day,
count(*) AS total,
count(*) FILTER (WHERE events.kind = 'purchase') AS purchases
FROM eventsSafe Raw SQL with exec_query + Binds
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;Laravel database migrations for schema management
class Tag < ApplicationRecord
has_many :taggings, dependent: :destroy
scope :top, ->(limit = 20) {
joins(:taggings)
.group(Arel.sql("tags.id"))Memory-Safe “top tags” aggregation with pluck + group
<?php
namespace App\Http\Requests;
use Illuminate\Foundation\Http\FormRequest;
use Illuminate\Validation\Rule;Laravel form requests for validation
Share this code
Here's the card — post it anywhere.
