Sort by pivot date

I'm in my Songs table

I have this column

Tables\Columns\TextColumn::make('latestSongHistory.played_at')
    ->sortable()

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

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

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

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

Sort by pivot date

I'm in my Songs table

I have this column

Tables\Columns\TextColumn::make('latestSongHistory.played_at')
    ->sortable()

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

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

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

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

Sort by pivot date

Sort by pivot date

Similar Threads

Similar Threads

Similar Threads