How to sort on HasOne relationship column in Laravel Filament table

Question

I have three models. Lead, LeadStatus and LeadUpdates. The Lead model represents a lead that is generated for a certain product. The LeadStatus table contains all the possible statusses (i.e. 'Created', 'Followed up' or 'Sale') that a Lead can have and the LeadUpdates table is a pivot table which keeps track of the status history of all leads.

In my Lead model I have a method to get the latest status update. This is what the method looks like:

public function latestStatusUpdate(): HasOne
{
    return $this->hasOne(LeadUpdate::class)->latestOfMany();
}

Now in my Filament panel I have a LeadResource which lists all Leads in a table. One of the columns in that table is the datetime of the latest status update.

Tables\Columns\TextColumn::make('latestStatusUpdate.created_at')
   ->label('Geüpdate op')
   ->dateTime('d-m-Y H:i')
   ->sortable(),

As you can see I chained the sortable() method which should make the table sortable on the created_at column of the latest status update, but for whatever reason sorting on that column doesn't work. When I click on the header (Geüpdate op), it sorts the table based on the id of the Lead either in ascending or descending order. How can I make it possible to order on the latest status update datetime.

Answer 1

I'm not sure how filament sort on the relationship like that, but you can customize sortable query like this:

->sortable(
    query: fn (Builder $query, string $direction) => $query
        ->orderByRaw(<<<SQL
            (
                SELECT created_at FROM lead_updates
                WHERE lead_updates.lead_id = lead.id
                ORDER BY created_at DESC
                LIMIT 1
            ) {$direction}
        SQL)
),

NB: adjust the table and column names