Filament•3w ago

How to sort table by relationship?

i have item model: name, created_by_id i have user model so i have the relationship in the item model: user (which returns the user who created the item) in the items filament table im showing the item's user's created_at field now, i want to be able to sort the items by the user created_at field. so i want to sort them by the relationship how can i achieve it?

11 Replies

Bruno Pereira•3w ago

TextColumn::make('user.created_at')
    ->dateTime()
    ->sortable()

TextColumn::make('user.created_at')
    ->dateTime()
    ->sortable()

If the table has the column and it has data, adding the sortable() should be enough

ericmpOP•3w ago

it works, thanks @Bruno Pereira but now lets change it a bit. imagine this setup: Song: id, name Play: id, song_id, user_id, played_at in the songs table i want to let the user sort by last time listened. Song model:

public function latestListenedPlay(): HasOne
{
    return $this->hasOne(Play::class)
        ->when(auth()->id(), fn ($q) => $q->where('user_id', auth()->id()))
        ->orderByDesc('played_at');
}

public function latestListenedPlay(): HasOne
{
    return $this->hasOne(Play::class)
        ->when(auth()->id(), fn ($q) => $q->where('user_id', auth()->id()))
        ->orderByDesc('played_at');
}

Songs filament table:

Tables\Columns\TextColumn::make('latestListenedPlay.played_at')
    ->label(__('Last time listened'))
    ->dateTime('d/m/Y H:i')
    ->sortable()

Tables\Columns\TextColumn::make('latestListenedPlay.played_at')
    ->label(__('Last time listened'))
    ->dateTime('d/m/Y H:i')
    ->sortable()

Issue when i click the button to sort:

Illuminate \ Database \ QueryException
PHP 8.3.13
11.33.2
SQLSTATE[21000]: Cardinality violation: 1242 Subquery returns more than 1 row
select
  *
from
  `songs`
order by
  (
    select
      `played_at`
    from
      `plays`
    where
      `songs`.`id` = `plays`.`song_id`
  ) asc
limit
  10 offset 0

Illuminate \ Database \ QueryException
PHP 8.3.13
11.33.2
SQLSTATE[21000]: Cardinality violation: 1242 Subquery returns more than 1 row
select
  *
from
  `songs`
order by
  (
    select
      `played_at`
    from
      `plays`
    where
      `songs`.`id` = `plays`.`song_id`
  ) asc
limit
  10 offset 0

ericmpOP•3w ago

im trying to setup the latestListenedPlay relationship in diff ways but idk how to fix it 🤷‍♂️ what would u try?

Bruno Pereira•3w ago

you probably have duplicate data in your table. And you shouldnt make queries modifying the relation.

public function play(): HasOne
{
    return $this->hasOne(Play::class);
}

public function latestListenedPlay(): Play
{
    return $this->play()->orderByDesc('played_at');
}

public function play(): HasOne
{
    return $this->hasOne(Play::class);
}

public function latestListenedPlay(): Play
{
    return $this->play()->orderByDesc('played_at');
}

But still don't know if it will work.

ericmpOP•3w ago

this:

public function latestListenedPlay(): Play
{
    return $this->play()->orderByDesc('played_at');
}

public function latestListenedPlay(): Play
{
    return $this->play()->orderByDesc('played_at');
}

doesnt return Play @Bruno Pereira

Bruno Pereira•3w ago

Pls don't tag people try play->

ericmpOP•3w ago

i wont tag u Call to a member function orderByDesc() on null using return $this->play->orderByDesc('played_at'); btw why i shouldnt make queries modifying the relation? and i dont have duplicate data in my table

Bruno Pereira•3w ago

Because you're defining a relation and then if you want to make custom attributes you use the element returned, its best practices. But you're free to do whatever you want what does dd($this->play) returns and dd($this->play())

ericmpOP•3w ago

i want to make custom attributes? hmm not sure bout that, i just want to retrieve the latest listened play as an eloquent relationship

Bruno Pereira•3w ago

or dynamic fields, whatever you want to call it

ericmpOP•2w ago

depends on the song, in one i tried, dd($this->play) returns the Play model so yeah the play relationship fn works and using the () i ovbiously get the relation: Illuminate\Database\Eloquent\Relations\HasOne^ {#2528 ... remember i need that function to return an eloquent relationship, i dont want a model cuz then i can work with relationships with the model i cannot work not sure if i explain myself bump bump bump bump

Gaming

Programming

How to sort table by relationship?