Can I sort table on non-column data

I know tables sort at the SQL level. That's why sorting like this does not work out of the box in Filament:

 TextColumn::make('days-past-due')->label('Past due')
                    ->color('danger')
                    ->visible(fn ($livewire) => $livewire->activePresetView == 'to-do-now')
                    ->sortable()
                    ->getStateUsing(function($record){
                        $followUpDate = $record->client_events->sortBy('follow_up_on')->firstWhere('is_complete', false)?->follow_up_on;
                        if($followUpDate){
                            $daysPastDue = ceil(now()->diffInDays($followUpDate));//will be negative so round up
                            return "$daysPastDue days";
                        }
                    }),

 TextColumn::make('days-past-due')->label('Past due')
                    ->color('danger')
                    ->visible(fn ($livewire) => $livewire->activePresetView == 'to-do-now')
                    ->sortable()
                    ->getStateUsing(function($record){
                        $followUpDate = $record->client_events->sortBy('follow_up_on')->firstWhere('is_complete', false)?->follow_up_on;
                        if($followUpDate){
                            $daysPastDue = ceil(now()->diffInDays($followUpDate));//will be negative so round up
                            return "$daysPastDue days";
                        }
                    }),

What I am doing above is presenting the oldest date (in days) to the user via that little internal query there. Does anybody have any idea on how to sort based on some information that is not stored in the table? I'm out of ideas.

7 Replies

awcodes•2w ago

The only way to do that is with a virtual column on the table.

bionaryOP•2w ago

Hi Adam! hmmm never new virtual column was a thing. I originally started planning a date based column...but making sure it reflected information based on other table changes was a nightmare.

bionaryOP•2w ago

Okay looking at this here: https://laraveldaily.com/post/virtual-db-columns-laravel-migrations-mysql I think you might be onto something Adam!

Laravel Daily

Virtual DB Columns in Laravel Migrations and MySQL

awcodes•2w ago

It’s in the filament docs, just can’t find it at the moment. Might be under the table filters docs.

bionaryOP•2w ago

Well that was a deep but unsuccessful rabbit hole. It turns out I cannot use virtual columns in this instance because a virtual column (MySQL) cannot reference data from other tables.

awcodes•2w ago

Is it a relationship? If it is you should be able to use sortable with the relationship dot syntax. Ie relation.name

bionaryOP•2w ago

Not directly. It's a calculated value based on a collection via a relationship. As in...give me the olded "past due date" related to this client and calculate that date from today in days (integer) I went back to my original idea and am using a Model Event. I seem to use those a lot and it's easy to forget about their magic when maintaining stuff later. The columns are written correctly but oddly filament is not sorting correctly. The new column does have negative numbers and nulls in there. Is that troublesome for Filament to sort? Ahhh it's getting late. I had a fixed sort on the query in...

    public static function getEloquentQuery(): Builder
    {
        return parent::getEloquentQuery()
...  sorting here duh....

    public static function getEloquentQuery(): Builder
    {
        return parent::getEloquentQuery()
...  sorting here duh....

All is right with my Filament universe at the moment 🙂

Gaming

Programming

Can I sort table on non-column data