Filament•9mo ago

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

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

17 Replies

mvenghaus•9mo ago

if you define the realationship in your model .. you don't have to do anything in table

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

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

ericmpOP•9mo ago

oh okay i added the relationship & did this:

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

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

but then i sort it and the records order is the same, they dont get sorted what im missing? at the moment i added the ->dateTime() just in case but nothing btw didnt know about latestOfMany @mvenghaus thanks for that! every day learning new stuff ^^

mvenghaus•9mo ago

yeah .. that's very powerful i would look at the query in debugbar .. maybe there is another sort in front with this filtering is also easy

->whereRelation('latestSongHistory', 'played_at', '<=', '...

->whereRelation('latestSongHistory', 'played_at', '<=', '...

ericmpOP•9mo ago

hmm okay, using debugbar i get:

SELECT *
FROM `songs`
ORDER BY `created_at` DESC, (
    SELECT `played_at`
    FROM `user_song_play_history`
        INNER JOIN (
            SELECT MAX(`user_song_play_history`.`id`) AS `id_aggregate`, `user_song_play_history`.`song_id`
            FROM `user_song_play_history`
            WHERE `user_song_play_history`.`song_id` IS NULL AND `user_song_play_history`.`song_id` IS NOT NULL
            GROUP BY `user_song_play_history`.`song_id`
        ) AS `latestOfMany`
    ON `latestOfMany`.`id_aggregate` = `user_song_play_history`.`id` AND `latestOfMany`.`song_id` = `user_song_play_history`.`song_id`
    WHERE `songs`.`id` = `user_song_play_history`.`song_id`
) ASC
LIMIT 10 OFFSET 0

SELECT *
FROM `songs`
ORDER BY `created_at` DESC, (
    SELECT `played_at`
    FROM `user_song_play_history`
        INNER JOIN (
            SELECT MAX(`user_song_play_history`.`id`) AS `id_aggregate`, `user_song_play_history`.`song_id`
            FROM `user_song_play_history`
            WHERE `user_song_play_history`.`song_id` IS NULL AND `user_song_play_history`.`song_id` IS NOT NULL
            GROUP BY `user_song_play_history`.`song_id`
        ) AS `latestOfMany`
    ON `latestOfMany`.`id_aggregate` = `user_song_play_history`.`id` AND `latestOfMany`.`song_id` = `user_song_play_history`.`song_id`
    WHERE `songs`.`id` = `user_song_play_history`.`song_id`
) ASC
LIMIT 10 OFFSET 0

at first sight, u see it fine? idk 🤔 hmmm, gotta try that too!

mvenghaus•9mo ago

do you have default sort on songs for created_at ? should be not default .. should be hard in a query played_at is on second position

ericmpOP•9mo ago

oh god must be that 100% cant check it right now but will do soon and i let u know @mvenghaus it worked, yeah i had a ->latest() (,: btw how would u use ->whereRelation in this case?

mvenghaus•9mo ago

this is a filter of mine .. very similar

ericmpOP•9mo ago

is this L11? or in L10 is available too?

mvenghaus•9mo ago

ericmpOP•9mo ago

oh okay

mvenghaus•9mo ago

long time

ericmpOP•9mo ago

is like using whereHas, right? similar i guess

mvenghaus•9mo ago

yeah .. but less code and more beautiful 🙂

ericmpOP•9mo ago

yes! ^^ thanks again 🙏

mvenghaus•9mo ago

you're welcome one last note .. wherehas is more modifiing the second select for data .. latestofmany uses a join .. that's why you can filter with whereRelation

ericmpOP•9mo ago

so u mean that with latestofmany i can filter and use whererelation and wherehas only modifies the second select's data?

mvenghaus•9mo ago

to be honest .. i'm not completly sure .. never tested latestofmany with wherehas 🙂 but that's how i use it

Gaming

Programming

How to make custom datetime column sortable?