Order by computed field

[slamcoderpt]

Package

Table builder

Package Version

v3.2

How can we help you?

In my code i would like to order by a computed value. Need some help to do it.
Does filament support front end sorting instead of backend?
`<?php

namespace App\Filament\Company\Widgets;

use App\Models\Device;
use App\Models\DeviceEvents;
use Filament\Tables\Columns\TextColumn;
use Filament\Tables\Table;
use Filament\Widgets\Concerns\InteractsWithPageFilters;
use Filament\Widgets\TableWidget as BaseWidget;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Carbon\Carbon;

class ReportTopTable extends BaseWidget
{
use InteractsWithPageFilters;

protected int $topCount = 10; // Number of records to show
protected string $sortBy = 'falls'; // Default sort

protected function getTableHeading(): string
{
    return '';
}

public function toggleSort(string $metric): void
{
    $this->sortBy = $metric;
}

protected function getFilters(): array
{
    return $this->getTableFilters();
}

public function getTableRecordKey(Model $record): string
{
    $groupBy = request()->input('components')[0]['updates']['filters.groupBy'] ?? 'device';
    return (string) ($groupBy === 'device' ? $record->SerialNumber : $record->user_id);
}

public function calculateUnlockedTimeByUser($companyId, $userId, $startDate, $endDate)
{
    // Fetch DEVICE_UNLOCKED and DEVICE_LOCKED events for the user in the given time range
    $events = DeviceEvents::where('user_id', $userId)
        ->whereBetween('event_time', [$startDate, $endDate])
        ->where('company_id', $companyId)
        ->whereIn('event_type', ['DEVICE_UNLOCKED', 'DEVICE_LOCKED'])
        ->orderBy('event_time')
        ->get();

    $totalTimeUnlocked = 0;
    $lastUnlockedTime = null;

    // Loop through the events
    foreach ($events as $event) {
        $eventTime = Carbon::parse($event->event_time); // Convert to Carbon instance

        if ($event->event_type === 'DEVICE_UNLOCKED') {
            // Store the time of the last unlock
            $lastUnlockedTime = $eventTime;
        } elseif ($event->event_type === 'DEVICE_LOCKED' && $lastUnlockedTime !== null) {
            // Ensure that the lock event happens after the unlock event
            if ($eventTime->greaterThan($lastUnlockedTime)) {
                // Calculate the time spent unlocked
                $timeSpentUnlocked = $eventTime->diffInSeconds($lastUnlockedTime);
                $totalTimeUnlocked += $timeSpentUnlocked;
                $lastUnlockedTime = null; // Reset after calculating the time
            }
        }
    }
    
    $totalTimeUnlocked = abs($totalTimeUnlocked);

    // Convert total time unlocked to human-readable format
    $days = floor($totalTimeUnlocked / 86400); // 1 day = 86400 seconds
    $hours = floor(($totalTimeUnlocked % 86400) / 3600); // 1 hour = 3600 seconds
    $minutes = floor(($totalTimeUnlocked % 3600) / 60);

    // Format as 1d:11h:23m
    return sprintf('%dd:%02dh:%02dm', $days, $hours, $minutes);
}

public function calculateUnlockedTime($companyId, $serialNumber, $startDate, $endDate)
{
    // Fetch DEVICE_UNLOCKED and DEVICE_LOCKED events for the device in the given time range
    $events = DeviceEvents::where('serial_number', $serialNumber)
        ->whereBetween('event_time', [$startDate, $endDate])
        ->where('company_id', $companyId)
        ->whereIn('event_type', ['DEVICE_UNLOCKED', 'DEVICE_LOCKED'])
        ->orderBy('event_time', 'asc')
        ->get();

    $totalTimeUnlocked = 0;
    $lastUnlockedTime = null;

    // Loop through the events
    foreach ($events as $event) {
        $eventTime = Carbon::parse($event->event_time);  // Convert to Carbon instance

        if ($event->event_type === 'DEVICE_UNLOCKED') {
            // Store the time of the last unlock
            $lastUnlockedTime = $eventTime;
        } elseif ($event->event_type === 'DEVICE_LOCKED' && $lastUnlockedTime !== null) {
            // Ensure that the lock event happens after the unlock event
            if ($eventTime->greaterThan($lastUnlockedTime)) {
                // Calculate the time spent unlocked
                $timeSpentUnlocked = $eventTime->diffInSeconds($lastUnlockedTime);
                $totalTimeUnlocked += $timeSpentUnlocked;
                $lastUnlockedTime = null;  // Reset after calculating the time
            }
        }
    }

    // Convert total time unlocked to human-readable format
    $totalTimeUnlocked = abs($totalTimeUnlocked);
    $days = floor($totalTimeUnlocked / 86400); // 1 day = 86400 seconds
    $hours = floor(($totalTimeUnlocked % 86400) / 3600); // 1 hour = 3600 seconds
    $minutes = floor(($totalTimeUnlocked % 3600) / 60);

    // Format as 1d:11h:23m
    return sprintf('%dd:%02dh:%02dm', $days, $hours, $minutes);
}

protected function getTableQuery(): Builder
{
    $filters = $this->filters;
    $startDate = $filters['startDate'] ?? now()->startOfYear();
    $endDate = $filters['endDate'] ?? now()->endOfYear();
    $groupBy = $filters['groupBy'] ?? 'device';
    
    $sortBy = $this->sortBy;

    $query = Device::query()
        ->selectRaw($groupBy === 'device' ? '
            devices.SerialNumber,
            devices.DeviceName,
            COUNT(CASE WHEN de.event_type = "APP_FALL" THEN 1 END) as Drops
        ' : '
            de.user_id,
            users.name as UserName,
            COUNT(CASE WHEN de.event_type = "APP_FALL" THEN 1 END) as Drops
        ')
        ->where('devices.company_id', auth()->user()->currentCompany->id)
        ->when($groupBy === 'user', function ($query) {
            $query->whereNotNull('de.user_id');
        })
        ->leftJoin('device_events as de', function($join) use ($startDate, $endDate) {
            $join->on('devices.SerialNumber', '=', 'de.serial_number')
                ->whereBetween('de.event_time', [$startDate, $endDate])
                ->where('de.company_id', auth()->user()->currentCompany->id);
        });

    if ($groupBy === 'user') {
        $query->leftJoin('users', 'de.user_id', '=', 'users.id')
            ->groupBy('de.user_id', 'users.name');
    } else {
        $query->groupBy('devices.SerialNumber', 'devices.DeviceName');
    }

    $query = match ($sortBy) {
        'topFalls' => $query->orderByDesc('Drops'),
        'topActivity' => $query->orderByDesc('UsageTime'),
        default => $query
    };

    $query = $query->limit($this->topCount);

    return $query;
}

public function table(Table $table): Table
{
    $groupBy = $this->filters['groupBy'] ?? 'device';
    $startDate = $filters['startDate'] ?? now()->startOfYear();
    $endDate = $filters['endDate'] ?? now()->endOfYear();

    return $table
        ->query($this->getTableQuery())
        ->columns([
            TextColumn::make($groupBy === 'device' ? 'DeviceName' : 'UserName')
                ->label($groupBy === 'device' ? 'Device' : 'User'),
            TextColumn::make($groupBy === 'device' ? 'SerialNumber' : 'user_id')
                ->label('Activity Time')
                ->visible(fn () => $this->sortBy == 'activity')
                ->formatStateUsing(fn ($state) => $groupBy == "device" ?  $this->calculateUnlockedTime(auth()->user()->currentCompany->id, $state, $startDate, $endDate) : $this->calculateUnlockedTimeByUser(auth()->user()->currentCompany->id, $state, $startDate, $endDate)),
            TextColumn::make('Drops')
                ->label('Drops')
                ->visible(fn () => $this->sortBy == 'falls'),
        ])
        ->paginated(false)
        ->headerActions([
            \Filament\Tables\Actions\Action::make('topFalls')
                ->label('Top Falls')
                ->icon('heroicon-m-arrow-trending-down')
                ->color(fn () => $this->sortBy == 'falls' ? 'primary' : "gray")
                ->action(fn () => $this->toggleSort('falls')),
            \Filament\Tables\Actions\Action::make('topActivity')
                ->label('Top Activity')
                ->icon('heroicon-m-clock')
                ->color(fn () => $this->sortBy == 'activity' ? 'primary' : "gray")
                ->action(fn () => $this->toggleSort('activity')),
        ])
        ->deferLoading();
}

}
`