FilamentF
Filament•2y ago
ericmp

How to make custom datetime column sortable?

im in my songs table. i want to sort the songs by the last time i played them
i have a pivot table between songs and users, there i store the played_at field, along with other relevant pivot attributes

so far i have this code:
Tables\Columns\TextColumn::make('last_time_listened')
    ->getStateUsing(function (Song $record): ?Carbon {
        $pivot = UserSongPlayHistory::query()
            ->latest()
            ->where('user_id', auth()->id())
            ->where('song_id', $record->id)
            ->where('is_listened', true)
            ->first();

        return $pivot?->played_at;
    })
    ->sortable(query: function (Builder $query, string $direction, $column): Builder {
        return $query
            ->join('user_song_play_history', 'user_song_play_history.song_id', '=', 'songs.id')
            ->orderBy('user_song_play_history.played_at');
    })


seems i have to join the tables somehow and then just sort by that field. the problem is that when i join them i get and idk how to solve it. to solve it i tried to select only the columns i needed from the pivot table, so i dont get the created_at field, but i couldnt get to do it:
SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in order clause is ambiguous


i also tried it this way but it doesnt work:
->sortable(query: function (Builder $query, string $direction, $column): Builder {
    return $query->whereHas('plays', function ($query) use ($direction) {
        $query->where('user_id', auth()->id());
        $query->where('user_song_play_history.is_listened', true);
        // $query->orderBy('user_song_play_history.played_at', $direction);
    })->orderBy('user_song_play_history.played_at', $direction);
})
Was this page helpful?