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

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');
    })

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

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);
})

->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);
})

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

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');
    })

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');
    })

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in order clause is ambiguous

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);
})

->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);
})

How to make custom datetime column sortable?

How to make custom datetime column sortable?

Similar Threads

Similar Threads

Similar Threads