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

ericmp•4mo ago

anyone ?

Saade•4mo 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?

ericmp•4mo 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•4mo ago

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

ericmp•4mo 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•4mo 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)

ericmp•4mo ago

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

Saade•4mo ago

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

Saade•4mo 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

ericmp•4mo 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•4mo 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

ericmp•4mo 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•4mo ago

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

ericmp•4mo ago

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

Saade•4mo ago

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

ericmp•4mo 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•4mo 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)
  )

ericmp•4mo ago

i think it works 🎊 im still testing it out though

ericmp•4mo ago

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

ericmp•4mo ago

but if u see the table:

ericmp•4mo 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 😂 🤦‍♂️

ericmp•4mo ago

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

Saade•4mo ago

Cool

ericmp•4mo 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•4mo 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

ericmp•4mo 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•4mo ago

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

ericmp•4mo 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•4mo ago

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

ericmp•4mo 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•4mo 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?

ericmp•4mo 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•4mo ago

Yes you can check that with debug bar.

ericmp•4mo 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

ericmp•4mo 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

ericmp•4mo ago

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

Tieme•4mo 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');

ericmp•4mo 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•4mo 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•4mo 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

ericmp•4mo 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

ericmp•4mo ago

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

Custom filters - Table Builder - Filament

Tieme•4mo 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']); 
      }]),
            );
    })

ericmp•4mo 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•4mo 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?

ericmp•4mo 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