Struggling filtering a table

Lets say i have this filament table:

return $table
->query(
    Song::query()
        ->whereHas('plays', function ($q) {
            $q->where('is_listened', true);
        })
        ->withCount('plays')
        ->orderByDesc('plays_count')
)

return $table
->query(
    Song::query()
        ->whereHas('plays', function ($q) {
            $q->where('is_listened', true);
        })
        ->withCount('plays')
        ->orderByDesc('plays_count')
)

the objective is to show the songs with more plays. eloquent relationships explanation: tables: songs: id, name artists: id, name artist_song: artist_id, song_id user_song_play_history: user_id, song_id, is_played relationships: Song model:

public function artists(): BelongsToMany
{
    return $this->belongsToMany(Artist::class)->withTimestamps();
}

public function plays(): BelongsToMany
{
    return $this->belongsToMany(User::class, UserSongPlayHistory::class);
}

public function artists(): BelongsToMany
{
    return $this->belongsToMany(Artist::class)->withTimestamps();
}

public function plays(): BelongsToMany
{
    return $this->belongsToMany(User::class, UserSongPlayHistory::class);
}

Artist model:

public function songs(): BelongsToMany
{
    return $this->belongsToMany(Song::class)->withTimestamps();
}

public function songs(): BelongsToMany
{
    return $this->belongsToMany(Song::class)->withTimestamps();
}

ArtistSong pivot model:

public function artist(): BelongsTo
{
    return $this->belongsTo(Artist::class);
}

public function song(): BelongsTo
{
    return $this->belongsTo(Song::class);
}

public function artist(): BelongsTo
{
    return $this->belongsTo(Artist::class);
}

public function song(): BelongsTo
{
    return $this->belongsTo(Song::class);
}

UserSongPlayHistory pivot model:

public function song(): BelongsTo
{
    return $this->belongsTo(Song::class);
}

public function user(): BelongsTo
{
    return $this->belongsTo(User::class);
}

public function song(): BelongsTo
{
    return $this->belongsTo(Song::class);
}

public function user(): BelongsTo
{
    return $this->belongsTo(User::class);
}

at the moment works just fine. the problem is when i try to add a filter to filter by user. i get and so far havent found how to solve it: Expected type 'Illuminate\Database\Eloquent\Builder'. Found 'Closure(mixed, mixed): void'

->filters([
    Tables\Filters\SelectFilter::make('user')
        ->relationship('plays.user', 'name')
        ->default([auth()->id()])
        ->apply(function ($query, $value) {
            $query->whereHas('plays', function ($q) use ($value) {
                $q->where('is_listened', true)
                    ->where('user_id', $value);
            });
        })
    ,
])

->filters([
    Tables\Filters\SelectFilter::make('user')
        ->relationship('plays.user', 'name')
        ->default([auth()->id()])
        ->apply(function ($query, $value) {
            $query->whereHas('plays', function ($q) use ($value) {
                $q->where('is_listened', true)
                    ->where('user_id', $value);
            });
        })
    ,
])

46 Replies

ericmpOP•10mo ago

anyone ?

Saade•10mo ago

Tables\Filters\SelectFilter::make('user')
  ->relationship(
      'plays.user',
      'name',
      fn (Builder $query) => $query->whereHas('plays', fn (Builder $q) => $q->where('is_listened', true));
   )
  ->default(auth()->id())

Tables\Filters\SelectFilter::make('user')
  ->relationship(
      'plays.user',
      'name',
      fn (Builder $query) => $query->whereHas('plays', fn (Builder $q) => $q->where('is_listened', true));
   )
  ->default(auth()->id())

Does this works for you?

ericmpOP•10mo ago

no, i get:

Filament\Tables\Filters\SelectFilter::getRelationship(): Return value must be of type Illuminate\Database\Eloquent\Relations\Relation|Illuminate\Database\Eloquent\Builder, null returned

Filament\Tables\Filters\SelectFilter::getRelationship(): Return value must be of type Illuminate\Database\Eloquent\Relations\Relation|Illuminate\Database\Eloquent\Builder, null returned

just when loading the dashboard

Saade•10mo ago

you're returning nothing from your relationship definition. You've probably forgot to add return on your relationship method

ericmpOP•10mo ago

i have cuz i set the return types on all the relationship fns just checked it, yeah all seems fine in the models fns

Saade•10mo ago

you cannot use plays.user as a relationship name, you're probably want to create a HasManyThrough to point directly to the users Song -> HasMany -> User -> Through -> ? plays (i guess)

ericmpOP•10mo ago

but is belongstomany not has many can i build it around a hasmanythrough? or there is a "belongstomanythrough" alternative? hmm

Saade•10mo ago

please describe the filtering you're trying to achive including the relation between them

Saade•10mo ago

https://github.com/staudenmeir/eloquent-has-many-deep

GitHub

GitHub - staudenmeir/eloquent-has-many-deep: Laravel Eloquent HasMa...

Laravel Eloquent HasManyThrough relationships with unlimited levels - GitHub - staudenmeir/eloquent-has-many-deep: Laravel Eloquent HasManyThrough relationships with unlimited levels

ericmpOP•10mo ago

in the filament dashboard im showing a table with the songs that have more plays this table doesnt take into account who actually listened the song. now, the user sees all plays from all the users. i want it to be able to see it filtering by them. a song is related to a user when a user listens to a song, in that moment a UserSongPlayHistory model is created (which contains user_id, song_id, is_played (boolean)) thanks for the suggestion, im currently trying to setup the relationship

Saade•10mo ago

isnt plays in the song model the only thing you need? plays is pointing to the user already through playhistory also, you dont need the is_played column, if there's a row in that table it already means that the user listened to the song. If the user never plays the song, the play history will never be created you'll never have a is_played that is false anyway

ericmpOP•10mo ago

not sure about this, can u elaborate more? yeah plays is the relationship between user and song (UserSongPlayHistory model) so u say that when setting up the select filter, in the relationship ishould only put 'plays' instead of 'user'? and about this, is needed, cuz i track the history, maybe the user arrived to one song, but skipped it, and they havent played it

Saade•10mo ago

understood i think so, and you probably want to chain ->withPivot('is_played') to the relationship

ericmpOP•10mo ago

i was checking it out, u deleted it? there was some problem?

Saade•10mo ago

i've wrote a redundant aproach, you can use your model as is, just use plays in the filter

ericmpOP•10mo ago

not sure what im doing wrong now doing:

Tables\Filters\SelectFilter::make('user')
    ->relationship(
        'plays',
        'name',
        fn ($query) => $query->whereHas('plays', fn ($q) => $q->where('is_listened', true)),
    )
    ->default(auth()->id())

Tables\Filters\SelectFilter::make('user')
    ->relationship(
        'plays',
        'name',
        fn ($query) => $query->whereHas('plays', fn ($q) => $q->where('is_listened', true)),
    )
    ->default(auth()->id())

i get: Call to undefined method App\Models\User::plays()

Saade•10mo ago

fn ($query) => $query->where('is_listened', true)

Tables\Filters\SelectFilter::make('user')
  ->relationship(
      'plays',
      'name',
       fn ($query) => $query->where('is_listened', true)
  )

Tables\Filters\SelectFilter::make('user')
  ->relationship(
      'plays',
      'name',
       fn ($query) => $query->where('is_listened', true)
  )

ericmpOP•10mo ago

i think it works 🎊 im still testing it out though

ericmpOP•10mo ago

oh no, it doesnt. something weird happens: i have created 3 rows in the user_song_play_history table:

ericmpOP•10mo ago

but if u see the table:

ericmpOP•10mo ago

i might be missing something here:

Tables\Columns\TextColumn::make('plays_count')->label('Times')

Tables\Columns\TextColumn::make('plays_count')->label('Times')

dont answer yet, ill try to fix it no, must be in the query itself:

->query(
    Song::query()
        ->whereHas('plays', function ($q) {
            $q->where('is_listened', true);
        })
        ->withCount('plays')
        ->orderByDesc('plays_count')
)

->query(
    Song::query()
        ->whereHas('plays', function ($q) {
            $q->where('is_listened', true);
        })
        ->withCount('plays')
        ->orderByDesc('plays_count')
)

somehow i have to tell that when i filter i want to actually filter 😂 🤦‍♂️

ericmpOP•10mo ago

and this is the screenshot filtering by the other user (user id 2)

Saade•10mo ago

Cool

ericmpOP•10mo ago

noo cool no, it doesnt work as expected xd, im near but no user tim should see only 1 time the song but is also grabbing the listen from the other user and eric sees the song "new memories" - times 2, when should be only 1 i keep investigating hmmmmmmm

Saade•10mo ago

well, you're half way there, i need to leave for a couple of hour now, i can help you later if you didnt found a solution

ericmpOP•10mo ago

okay go, yeah ill keep pushing lets see if i get it brigado 🙌 🙌 🙌 🙌 🙌 so ive modified the table:

public function table(Table $table): Table
{
    return $table
        ->query(
            Song::query()
                ->withCount([
                    'plays' => function ($q) {
                        $q->where('is_listened', true);
                    },
                ])
                ->orderByDesc('plays_count')
        )
        ->columns([
            Tables\Columns\TextColumn::make('plays_count')
                ->label(__('Times'))
            ,
            Tables\Columns\SpatieMediaLibraryImageColumn::make('cover')
                ->collection('cover')
                ->circular()
            ,
            Tables\Columns\TextColumn::make('name')
                ->searchable()
                ->sortable()
            ,
            \App\Tables\Columns\ArtistsColumn::make('artists'),
        ])
        ->filters([
            Tables\Filters\SelectFilter::make('user')
                ->relationship('plays', 'name')
            ,
        ])
    ;
}

public function table(Table $table): Table
{
    return $table
        ->query(
            Song::query()
                ->withCount([
                    'plays' => function ($q) {
                        $q->where('is_listened', true);
                    },
                ])
                ->orderByDesc('plays_count')
        )
        ->columns([
            Tables\Columns\TextColumn::make('plays_count')
                ->label(__('Times'))
            ,
            Tables\Columns\SpatieMediaLibraryImageColumn::make('cover')
                ->collection('cover')
                ->circular()
            ,
            Tables\Columns\TextColumn::make('name')
                ->searchable()
                ->sortable()
            ,
            \App\Tables\Columns\ArtistsColumn::make('artists'),
        ])
        ->filters([
            Tables\Filters\SelectFilter::make('user')
                ->relationship('plays', 'name')
            ,
        ])
    ;
}

the issue is that the plays count doesnt get modified when filtering by the user how can i achieve it? any ideas?

Tieme•10mo ago

What do you mean by plays_count is not modified when filtering by User?

ericmpOP•10mo ago

i mean that i'd like to modify it when filtering the user. let me explain. when i apply a filter by a user, that plays_count is wrong cuz is including all plays. and somehow i want to include only the user's, but idk how to 🤷‍♂️

Tieme•10mo ago

How does the model 'user' looks like? This is you Table?

ericmpOP•10mo ago

yes not sure if u mean the eloquent relationship: inside user class:

public function plays(): BelongsToMany
{
    return $this->belongsToMany(Song::class, UserSongPlayHistory::class)
        ->withTimestamps()
        ->withPivot('is_listened')
    ;
}

public function plays(): BelongsToMany
{
    return $this->belongsToMany(Song::class, UserSongPlayHistory::class)
        ->withTimestamps()
        ->withPivot('is_listened')
    ;
}

Tieme•10mo ago

Your Table is for Song Model and not User model, depends on how all is setup. What is the query what is being used if you filter?

ericmpOP•10mo ago

should i change the query to User model then? 🤔

What is the query what is being used if you filter?

not sure how to check that, with debugbar?

Tieme•10mo ago

Yes you can check that with debug bar.

ericmpOP•10mo ago

to ur question about what is the query: without filtering;

SELECT `songs`.*,

  (SELECT count(*)
   FROM `users`
   INNER JOIN `user_song_play_history` ON `users`.`id` = `user_song_play_history`.`user_id`
   WHERE `songs`.`id` = `user_song_play_history`.`song_id`
     AND `is_listened` = 1) AS `plays_count`
FROM `songs`
ORDER BY `plays_count` DESC
LIMIT 5
OFFSET 5

SELECT `songs`.*,

  (SELECT count(*)
   FROM `users`
   INNER JOIN `user_song_play_history` ON `users`.`id` = `user_song_play_history`.`user_id`
   WHERE `songs`.`id` = `user_song_play_history`.`song_id`
     AND `is_listened` = 1) AS `plays_count`
FROM `songs`
ORDER BY `plays_count` DESC
LIMIT 5
OFFSET 5

ericmpOP•10mo ago

filtering by user with id of 1:

SELECT `songs`.*,

  (SELECT count(*)
   FROM `users`
   INNER JOIN `user_song_play_history` ON `users`.`id` = `user_song_play_history`.`user_id`
   WHERE `songs`.`id` = `user_song_play_history`.`song_id`
     AND `is_listened` = 1) AS `plays_count`
FROM `songs`
WHERE (EXISTS
         (SELECT *
          FROM `users`
          INNER JOIN `user_song_play_history` ON `users`.`id` = `user_song_play_history`.`user_id`
          WHERE `songs`.`id` = `user_song_play_history`.`song_id`
            AND `users`.`id` = '1'))
ORDER BY `plays_count` DESC
LIMIT 5
OFFSET 0

SELECT `songs`.*,

  (SELECT count(*)
   FROM `users`
   INNER JOIN `user_song_play_history` ON `users`.`id` = `user_song_play_history`.`user_id`
   WHERE `songs`.`id` = `user_song_play_history`.`song_id`
     AND `is_listened` = 1) AS `plays_count`
FROM `songs`
WHERE (EXISTS
         (SELECT *
          FROM `users`
          INNER JOIN `user_song_play_history` ON `users`.`id` = `user_song_play_history`.`user_id`
          WHERE `songs`.`id` = `user_song_play_history`.`song_id`
            AND `users`.`id` = '1'))
ORDER BY `plays_count` DESC
LIMIT 5
OFFSET 0

ericmpOP•10mo ago

no, this is a songs table, not a users table

Tieme•10mo ago

You are filtering the main query, you also need to filter the subquery dont know if that is possible with filters.

Song::query()
      ->withCount(['plays' => function ($q) use ($user_id) {
          $q->where('is_listened', true)
            ->where('user_id', $user_id); // Filter plays by the specific user ID
      }])
      ->orderByDesc('plays_count');

Song::query()
      ->withCount(['plays' => function ($q) use ($user_id) {
          $q->where('is_listened', true)
            ->where('user_id', $user_id); // Filter plays by the specific user ID
      }])
      ->orderByDesc('plays_count');

ericmpOP•10mo ago

hmm and from where i am suposed to get the $user_id variable? not sure how to retrieve it and seems the code u gave me was given from an ai (nothing wrong, but it seems that xd)

Tieme•10mo ago

True, it was ChatGPT inserted your SQL query and let me explain what is does and what the problem was. Than explains to me how to sole this. (and yes i know what it does but needed to know if the gpt know wat is was)

Tieme•10mo ago

I see https://filamentphp.com/docs/3.x/tables/filters/getting-started#modifying-the-base-query dont know if this works on SelectFilter and how to get the SelectFilter value

Getting started - Table Builder - Filament

ericmpOP•10mo ago

i think i have to modify the filter and there, apply custom query or something like that but idk how to do it exactly

ericmpOP•10mo ago

im here now - https://filamentphp.com/docs/3.x/tables/filters/custom

Custom filters - Table Builder - Filament

Tieme•10mo ago

I think something like this

Filter::make('user')
    ->form([
        Select::make('user')
        ->relationship('plays', 'name'),
    ])
    ->query(function (Builder $query, array $data): Builder {
        return $query
            ->when(
                $data['user'],
                fn (Builder $query, $data): Builder => $query->withCount(['plays' => function ($q) use ($data) {
          $q->where('is_listened', true)
            ->where('user_id', $data['user']); 
      }]),
            );
    })

Filter::make('user')
    ->form([
        Select::make('user')
        ->relationship('plays', 'name'),
    ])
    ->query(function (Builder $query, array $data): Builder {
        return $query
            ->when(
                $data['user'],
                fn (Builder $query, $data): Builder => $query->withCount(['plays' => function ($q) use ($data) {
          $q->where('is_listened', true)
            ->where('user_id', $data['user']); 
      }]),
            );
    })

ericmpOP•10mo ago

im trying it Undefined array key "user" also tried to say:

$data['user'] ?? null

$data['user'] ?? null

but then seems it doesnt do any filter

Tieme•10mo ago

if you dd($data)

    ->query(function (Builder $query, array $data): Builder {
        dd($data);
        return $query

    ->query(function (Builder $query, array $data): Builder {
        dd($data);
        return $query

What is in the data?

ericmpOP•10mo ago

array:1 [▼ // app\Filament\Widgets\TopListenedSongsTable.php:82
  "value" => null
]

array:1 [▼ // app\Filament\Widgets\TopListenedSongsTable.php:82
  "value" => null
]

i guess we need to get value instead of user? now the query looks like this:

SELECT `songs`.*,

  (SELECT count(*)
   FROM `users`
   INNER JOIN `user_song_play_history` ON `users`.`id` = `user_song_play_history`.`user_id`
   WHERE `songs`.`id` = `user_song_play_history`.`song_id`
     AND `is_listened` = 1) AS `plays_count`
FROM `songs`
ORDER BY `plays_count` DESC
LIMIT 5
OFFSET 10

SELECT `songs`.*,

  (SELECT count(*)
   FROM `users`
   INNER JOIN `user_song_play_history` ON `users`.`id` = `user_song_play_history`.`user_id`
   WHERE `songs`.`id` = `user_song_play_history`.`song_id`
     AND `is_listened` = 1) AS `plays_count`
FROM `songs`
ORDER BY `plays_count` DESC
LIMIT 5
OFFSET 10

and the filter:

Tables\Filters\SelectFilter::make('user')
    ->relationship('plays', 'name')
    ->query(function (Builder $query, array $data): Builder {
        return $query->when(($data['value'] ?? null) > 0, function (Builder $query) use ($data) {
            $query->withCount(['plays' => function ($q) use ($data) {
                $q->where('is_listened', true);
                $q->where('user_song_play_history.user_id', $data['value']);
            }]);
        });
    })

Tables\Filters\SelectFilter::make('user')
    ->relationship('plays', 'name')
    ->query(function (Builder $query, array $data): Builder {
        return $query->when(($data['value'] ?? null) > 0, function (Builder $query) use ($data) {
            $query->withCount(['plays' => function ($q) use ($data) {
                $q->where('is_listened', true);
                $q->where('user_song_play_history.user_id', $data['value']);
            }]);
        });
    })

i cant see where is filtering by the user here 🤷‍♂️ though im filtering in the table and the condition is true and $data['value'] is 1 (user id 1) might be missing something now ive done this test: in the table query function i hardcoded it: so now im filtering by the user with id 1

->query(
    Song::query()
        ->withCount([
            'plays' => function ($q) {
                $q->where('is_listened', true);
                $q->where('user_song_play_history.user_id', 1);
            },
        ])
        ->orderByDesc('plays_count')
)

->query(
    Song::query()
        ->withCount([
            'plays' => function ($q) {
                $q->where('is_listened', true);
                $q->where('user_song_play_history.user_id', 1);
            },
        ])
        ->orderByDesc('plays_count')
)

and this way works but with the filters it doesnt i guess it has something to do with withCount, which doesnt work on filters or im doing something wrong any ideas?

Gaming

Programming

Struggling filtering a table