Subquery in table filter

this is the query:

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

im trying to show the song name, and how many times the song is listened. like this, it works. the problem comes when i want to filter by certain user:

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

it just doesnt work. the field to be filtered is the

user_song_play_history.user_id

user_song_play_history.user_id

. is this the right way to do it?

if i manually filter it (uncommenting the commented line in the query fn) works, but if i filter via the gui, it doesnt

old question: ❓┊helpStruggling filtering a table

ericmpOP•2/20/24, 4:25 PM

no one? plz let me know if i should re ask it better / provide more code so is easy to understand my question ^^

Tieme•2/20/24, 6:17 PM

I think you need to rephrase your question to something like

Subquery in table filter, that is wat you need in my opinion.

TTieme I think you need to rephrase your question to something like *Subquery in tabl...

ericmpOP•2/21/24, 7:26 AM

thanks, lets see now ^^

gladjanus43•2/21/24, 8:25 AM

what happens when you log $data['value'] somewhere? What is the value of it when you filter on a user?
You can do something like error_log($data['value']) and it logs it into the terminal where you have served your local environment

ericmpOP•2/21/24, 2:29 PM

if i remember well was null always

cant test it now cuz i dont have the project here, but when i can ill retry it

ericmpOP•2/21/24, 2:30 PM

didnt know this trick - error_log()

ericmpOP•2/21/24, 2:30 PM

but im running it via nginx not php artisan serve

gladjanus43•2/21/24, 2:35 PM

You should be able to dump the result somewhere! dd() will not work i think because it will run on page load and will not let you set the filter

ericmpOP•2/21/24, 2:37 PM

okay, ill use then yeah, to log it in laravel.log file

ericmpOP•2/21/24, 2:38 PM

so last time i used dd, that is maybe why always was null

gladjanus43•2/21/24, 2:40 PM

Dont know for sure that it will block your filter, but can be a start...

ericmpOP•2/21/24, 2:41 PM

in a few hours ill be able to test it again, ill post the results here

ericmpOP•2/21/24, 6:18 PM

this is what im getting when i filter

Eericmp this is what im getting when i filter

Tieme•2/21/24, 7:23 PM

You are missing the with the thats why you are getting the error.

ericmpOP•2/22/24, 6:16 AM

ericmpOP•2/22/24, 6:22 AM

okay now:
with this code:

in :

but the table doesnt change, i see the same records & same values. it doesnt change

gladjanus43•2/22/24, 10:51 AM

return $query
->when(($data['value'] ?? null), 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 am not sure but dont you want the user to be filtered outside of the when clause? The user filter has nothing to do with the count you are making right

Ggladjanus43 return $query ->when(($data['value'] ?? null), function (Builder $qu...

ericmpOP•2/22/24, 6:44 PM

if i filter outside im filtering in the songs table, i want to filter the pivot field

ericmpOP•2/24/24, 7:05 PM

no ideas? (: