FilamentF
Filament•2y ago
ericmp

Sort by pivot date

I'm in my Songs table

I have this column
Tables\Columns\TextColumn::make('latestSongHistory.played_at')
    ->sortable()


but when i sort it, it doesnt crash, but i dont see the dates sorted

i have 3 tables
song: id, name
user_song_play_history: song_id, user_id, played_at
user: id, name
public function latestSongHistory(): HasOne
{
    return $this->hasOne(UserSongPlayHistory::class)->latestOfMany();
}


is there any way to achieve this sorting?

i also tried:
Tables\Columns\TextColumn::make('latestSongHistory.played_at')
    // ->sortable()
    ->sortable(query: function (Builder $query, string $direction): Builder {
        dd($query->toSql());
        return $query->orderBy('user_song_play_history.played_at', $direction);
        // return $query->orderBy('played_at', $direction);
        // return $query->orderBy('latestSongHistory.played_at', $direction);
    })
Solution
public static function getEloquentQuery(): Builder
    {
        return parent::getEloquentQuery()
            ->select('songs.*', DB::raw('(select played_at from user_song_play_history where songs.id = song_id order by played_at desc limit 1) as latest_played_at'));
    }
Jump to solution
Was this page helpful?