2 years ago

Category: Laravel, Laravel, PHP, PHP, MySQL, MySQL, api, api

Check out my short series on Laravel Reports: Part https://blog.deleu.dev/laravel-report-dynamic-page-size/. A few months back I sent a slack message to my team celebrating 3 years since I started a brand new Laravel project dedicated for our reporting service.

final class PurchaseTicketCorrelationTrendInput { public EloquentTrend $trend; public function __construct(TicketPeriod $period, Interval $interval) { $this->trend = new EloquentTrend($interval, $period); }}

final class EloquentTrend { public function __construct( private Interval $interval, private Period $period, ) {} public function __invoke(Eloquent|Builder $builder) { $builder->tap($this->trendInterval())->tap(new EloquentPeriodScope($this->period)); } public function datapoints(Closure $callback): array { $rows = []; $carbonPeriod = CarbonPeriod::create($this->period->start(), $this->increment(), $this->period->end(), CarbonPeriod::IMMUTABLE); $datapoints = $carbonPeriod->toArray(); foreach ($datapoints as $datapoint) { $rows[] = $callback($datapoint); } return $rows; } private function trendInterval(): TimeSeries { return match ($this->interval->toString()) { 'day' => new GroupByDay($this->period->field()), 'week' => new GroupByWeek($this->period->field()), 'month' => new GroupByMonth($this->period->field()), 'quarter' => new GroupByQuater($this->period->field()), 'year' => new GroupByYear($this->period->field()), default => throw new InvalidArgumentException("Invalid parameter interval with value [$interval]"), }; } private function increment(): string { return match ($this->interval->toString()) { 'day' => '1 day', 'week' => '1 week', 'month' => '1 month', 'quarter' => '3 months', 'year' => '1 year', default => throw new InvalidArgumentException("Invalid parameter interval with value [$interval]"), }; }} final class EloquentPeriodScope { public function __construct(private Period $period) {} public function __invoke(Eloquent|Builder $builder): void { $builder->when($this->period->valid(), function (Eloquent|Builder $builder) { $builder->whereBetween( $this->period->field(), [$this->period->start(), $this->period->end(), ); }); }} final class GroupByDay implements TimeSeries { public function __construct(private string $field) {} public function __invoke(Builder $builder): void { $builder->selectRaw("DATE_FORMAT($this->field,'%Y-%m-%d') as `period`") ->groupBy('period'); }} final class GroupByWeek implements TimeSeries { public function __construct(private string $field) {} public function __invoke(Builder $builder): void { $builder->selectRaw("DATE_FORMAT($this->field, '%x w%v') as `period`") ->groupBy('period'); }} final class GroupByMonth implements TimeSeries { public function __construct(private string $field) {} public function __invoke(Builder $builder): void { $builder->selectRaw("DATE_FORMAT($this->field, '%Y-%m') as `period`") ->groupBy('period'); }} final class GroupByQuarter implements TimeSeries { public function __construct(private string $field) {} public function __invoke(Builder $builder): void { $builder->selectRaw("CONCAT(YEAR($this->field), ' Q', QUARTER($this->field)) as `period`") ->groupBy('period'); }} final class GroupByYear implements TimeSeries { public function __construct(private string $field) {} public function __invoke(Builder $builder): void { $builder->selectRaw("DATE_FORMAT($this->field, '%Y') as `period`") ->groupBy('period'); }}

private function aggregateTicketsBuilder(): Builder { $expression = new AggregatePeriodComparisonExpression($this->period); return $this->builder->tickets() ->tap($expression->count('aggregate')) ->withCasts([ 'value' => 'int', 'current_aggregate' => 'int', 'previous_aggregate' => 'int', ]); } final class AggregatePeriodComparisonExpression { protected array $expressions = []; protected array $bindings = []; public function __construct(private Period $period) {} public function count(string $alias): self { $field = $this->period->field(); $this->expressions[] = "SUM(IF($field BETWEEN ?