How to default sort by column that does not exist in the table?

[waqasraza123]

I am generating a custom column in my table that sums the amount column. So, technically the sum column does not exist in the database and a temporary column. But I want to default sort by the sum column.

Tables\Columns\TextColumn::make('fines_sum_amount')
                    ->label(__('Total Fine Amount'))
                    ->sum("fines", "amount")
                    ->prefix("$")
                    ->sortable()
                    ->default(0),

Calling defaultSort works.

->defaultSort("fines_sum_amount", "desc");

but when i use any bulk actions, I get the error that column fines_sum_amount does not exist in the database.

and here is my bulk action

Tables\Actions\BulkAction::make("user standing")
                    ->translateLabel()
                    ->action(
                        function(Collection $records){
                            //$updateUserStanding = new UpdateUserStanding($records);
                            //dispatch($updateUserStanding);
                        }
                    )
                    ->form([
                        Forms\Components\Select::make('social_status')
                            ->label('Select Social Status')
                            ->options([
                                "no_social_events" => "No Social Events"
                            ])
                            ->required(),
                    ])
                    ->requiresConfirmation()
                    ->modalHeading('User Social Standing')
                    ->modalSubheading('Update the selected user(s) social standing.')
                    ->modalButton('Update')
                    ->color('success')
                    ->icon('heroicon-o-check'),

error

SQLSTATE[42703]: Undefined column: 7 ERROR: column "fines_sum_amount" does not exist

[danharrin]

We can't sort by a column that does not exist in the database because it would be very inefficient across large datasets. Maybe you need a generated column in the database

[dennyvik]

I know it's been a long time ago, but maybe someone might need the solution.
Here how i solve this.

return $table
    ->modifyQueryUsing(function ($query) {
        $subQuery = DB::table('registration_activities')
                    ->select('registration_id', DB::raw('MAX(created_at) as newest'))
                    ->groupBy('registration_id');

        return $query->joinSub($subQuery, 'latest_status_updates', function ($join) {
            $join->on('registrations.id', '=', 'latest_status_updates.registration_id');
        })
        ->orderByRaw("
            CASE
                WHEN registrations.status IN ('REGISTERED', 'CANCEL', 'PENDING') THEN 1
                ELSE 0
            END
        ");
    })
    ->defaultSort('latest_status_updates.newest', 'asc')

So, this is inside RegistrationResource which has Registration model.
registrations table has many relationship to registration_activities

I need Registration list to be sorted by default based on it's latest registration_activities.

Then in a table column, i displayed the date differences from now and make it sortable as well :

TextColumn::make('followup_since')
    ->label('Last FU')
    ->sortable(query: function(Builder $query, string $direction): Builder {
        return $query->orderBy('latest_status_updates.newest', $direction);
    })
    ->getStateUsing(function ($record) {
        $latestStatusUpdate = $record->status_updates()->latest()->first();

        if ($latestStatusUpdate) {
            return $latestStatusUpdate->created_at->diffForHumans(now(), CarbonInterface::DIFF_ABSOLUTE, true);
        }
    }),

• disclaimer : i don't know if this will affected the speed to load the list.