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:

```PHP 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

24 Replies

ericmp•2mo ago

bump bump

Tally•2mo ago

Hi Eric, I tried it... I see that the ->latestOfMany is the cullprit If you change your function to this it will work

public function latestSongHistory(): HasMany
{
    return $this->hasMany(UserSongPlayHistory::class)->orderBy('played_at', 'desc')->limit(1);
}

public function latestSongHistory(): HasMany
{
    return $this->hasMany(UserSongPlayHistory::class)->orderBy('played_at', 'desc')->limit(1);
}

maybe there is a more elegant solution? But for the time being this works 🙂

ericmp•2mo ago

thanks for ur answer! @Tally but hmm im gettin this when i click the sort icon in the table now

ericmp•2mo ago

to try to solve it i modified the fn once again, now trying it this way:

public function latestSongHistory(): HasOne
{
    return $this->hasOne(UserSongPlayHistory::class)->latestOfMany('played_at');
}

public function latestSongHistory(): HasOne
{
    return $this->hasOne(UserSongPlayHistory::class)->latestOfMany('played_at');
}

but it still doesnt work as i expect

ericmp•2mo ago

for example: querystring: /songs?tableSortColumn=latestSongHistory.played_at&tableSortDirection=desc

ericmp•2mo ago

but the dates arent sorted properly by the last time listened date field

ericmp•2mo ago

/songs?tableSortColumn=latestSongHistory.played_at&tableSortDirection=asc and asc seems it doesnt neither

awcodes•2mo ago

Something is off in your relationship. It can’t be a HasOne with a latest of many.

ericmp•2mo ago

why not?

Dennis Koch•2mo ago

Yeah, why not? 😅

Tally•2mo ago

hmmm must be possible to add an order and a limit to the order by subquery

ericmp•2mo ago

how that could be achieved? u mean modifying somehow the query u provided? not sure why in the error limit 10 is shown when limit is set to 1

Tally•2mo ago

It's difficult debugging the actual code without a testrepo The limit 10 is outside of the subquery (it's the default table limit)

ericmp•2mo ago

oh okay yeah makes sense so, if i create a test repo, would u mind checking it out? ill set it all up ez to reproduce

Tally•2mo ago

yeah no problem

ericmp•2mo ago

perfect, i guess today afternoon ill be able to reproduce it in a repo, then ill ping u 🙌 thanks again @Tally i finally was able to create the repo https://github.com/ericmp33/filatest/blob/main/README.md when u up to just let me know 🙌

Tally•2mo ago

shouldn't be so difficult you would think... ended up with this to get it working

Solution

Tally•2mo ago

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

Tally•2mo ago

Tables\Columns\TextColumn::make('latest_played_at')
  ->label(__('Last time listened'))
  ->dateTime('d/m/Y H:i')
  ->sortable()
  ->description(fn(?string $state): ?string => Carbon::make($state)?->diffForHumans())

Tables\Columns\TextColumn::make('latest_played_at')
  ->label(__('Last time listened'))
  ->dateTime('d/m/Y H:i')
  ->sortable()
  ->description(fn(?string $state): ?string => Carbon::make($state)?->diffForHumans())

maybe something for a real Filament Pro dev 😉

ericmp•2mo ago

no way! seems it works, im implementing it

Tally•2mo ago

pfff yeah

ericmp•2mo ago

so u went to the raw sql nice

Tally•2mo ago

It'll work... but it's not the Filament way... had something to do with the way Filament/Laravel does the ordering with related fields 😉 If someone else has a nice idea you can always refactor 😉 good luck mate

ericmp•2mo ago

oh i see, well the trick u did is valid to me (: im wondering if i can somehow convert the query to a virtual column. ill search that. it may be an improvement, not sure though. gotta search how sql virtual columns actually work and how capable are to do this type of stuff yeah absolutely, thanks a lot appreciate the time u spent on this 🙌

Gaming

Programming

Sort by pivot date