Laravel Query optimization, and efficient Way to Calculate Employee KPIs

Hi everyone,

I'm working on a system to calculate employee KPIs based on the actions they take during negotiations, this functionality contains returning of KPI and percentage of employee's kpi, according its created action, i am creating KPI here choose action option and quantity of each action option and add, after i should calculate of each kpi item according employees created actions which has also action option. I have the following models

Employee:

<?php

namespace App\Models;

use App\Traits\HasDeletionStatusFilter;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Database\Eloquent\SoftDeletes;

class Employee extends Model
{
    use HasDeletionStatusFilter, HasFactory, SoftDeletes;

    protected $fillable = [
        'personal_number',
        'first_name',
        'last_name',
        'is_active',
        'user_id',
        'phone_number',
        'email',
        'profile_image',
        'deleted_by',
    ];

    protected $appends = ['full_name'];

    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class);
    }

    public function negotiations(): HasMany
    {
        return $this->hasMany(Negotiation::class, 'manager_id');
    }

    public function actions(): HasMany
    {
        return $this->hasMany(Action::class, 'created_by_employee_id');
    }
}

Action:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\SoftDeletes;

class Action extends Model
{
    use HasFactory, SoftDeletes;

    protected $fillable = [
        'title',
        'connection_status',
        'contact_date',
        'next_contact_date',
        'next_meeting_date',
        'duration_minutes',
        'connection_description',
        'email_offer_doc_to_contact_person',
        'connection_type',
        'created_by_employee_id',
        'action_option_id',
        'negotiation_id',
    ];

   

  

    public function createdByEmployee(): BelongsTo
    {
        return $this->belongsTo(Employee::class, 'created_by_employee_id');
    }

    public function actionOption(): BelongsTo
    {
        return $this->belongsTo(ActionOption::class);
    }

   

    public function negotiation(): BelongsTo
    {
        return $this->belongsTo(Negotiation::class);
    }
}

ActionOption

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasMany;

class ActionOption extends Model
{
    protected $fillable = ['name', 'buyer_organization_id'];

    public function buyerOrganization(): BelongsTo
    {
        return $this->belongsTo(BuyerOrganization::class);
    }

    public function actions(): HasMany
    {
        return $this->hasMany(Action::class);
    }
}

Kpi:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasMany;

class Kpi extends Model
{
    protected $fillable = ['employee_id', , 'kpi_date', ];

    public function employee(): BelongsTo
    {
        return $this->belongsTo(Employee::class);
    }

   

    public function kpiItems(): HasMany
    {
        return $this->hasMany(KPIItem::class);
    }
}

and KpiItem

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class KpiItem extends Model
{
    protected $fillable = ['kpi_id', 'action_option_id', 'assigned_quantity'];

    public function kpi(): BelongsTo
    {
        return $this->belongsTo(KPI::class);
    }

    public function actionOption(): BelongsTo
    {
        return $this->belongsTo(ActionOption::class);
    }
}

this is logic of creation, and getting of calculated kpi data according its items and created actions:

<?php

namespace App\Services\Kpi;

use App\Models\Kpi;
use App\Models\KpiItem;
use Illuminate\Support\Facades\DB;

class KpiService
{
    public function create(array $data): Kpi
    {
        return DB::transaction(function () use ($data) {
            $kpi = new Kpi;
            $kpi->fill($data);
            $kpi->save();

            if (! empty($data['items'])) {
                foreach ($data['items'] as $item) {
                    $kpiItem = new KpiItem;
                    $kpiItem->fill($item);
                    $kpiItem->kpi_id = $kpi->id;
                    $kpiItem->save();
                }
            }

            return $kpi;
        });
    }

    public function getKpiActionsByEmployee(int $employeeId, ?string $date = null)
    {
        $query = Kpi::with(['kpiItems.actionOption.actions'])
            ->where('employee_id', $employeeId);

        if ($date) {
            $query->whereDate('kpi_date', $date);
        }

        $kpis = $query->get();

        if (!$kpis) {
            throw new \Exception('No KPIs found for the given employee.');
        }

        return $this->formatKpiData($kpis, $date);
    }


    /**
     * Format KPI data to include action percentages based on assigned quantity and actions created.
     *
     * @param Kpi $kpi
     * @return array
     */
    private function formatKpiData($kpis, ?string $date): array
    {
        $aggregatedData = collect();

        foreach ($kpis as $kpi) {
            $kpi->kpiItems->each(function ($kpiItem) use (&$aggregatedData, $date) {
                $actionOptionName = $kpiItem->actionOption->name;

                if (!$aggregatedData->has($actionOptionName)) {
                    $aggregatedData->put($actionOptionName, [
                        'action_option_name' => $actionOptionName,
                        'total_assigned_quantity' => 0,
                        'total_actions_created' => 0,
                    ]);
                }

                $currentData = $aggregatedData->get($actionOptionName);

                $currentData['total_assigned_quantity'] += $kpiItem->assigned_quantity;

                $currentData['total_actions_created'] += $kpiItem->actionOption->actions
                    ->filter(function ($action) use ($date) {
                        return $action->created_at->toDateString() === $date;
                    })
                    ->count();

                $aggregatedData->put($actionOptionName, $currentData);
            });
        }

        $aggregatedData = $aggregatedData->map(function ($data) {
            $data['percentage_created'] = $data['total_assigned_quantity'] > 0
                ? round(($data['total_actions_created'] / $data['total_assigned_quantity']) * 100, 2)
                : 0;
            return $data;
        });

        return $aggregatedData->values()->toArray();
    }



}

• what is the best way to do functionality and calculations like this, Are there any potential optimizations I might be overlooking.
• Is there a more efficient way to achieve the same result using Laravel's query builder or other techniques? -Has anyone else implemented a similar KPI calculation for actions in their Laravel applications? Sharing experiences and alternative approaches would be greatly appreciated.