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. 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: https://discord.com/channels/883083792112300104/1204136992288407612

15 Replies

ericmp•9mo ago

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

Tieme•9mo ago

I think you need to rephrase your question to something like Subquery in table filter, that is wat you need in my opinion.

ericmp•8mo ago

thanks, lets see now ^^

gladjanus43•8mo ago

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

ericmp•8mo ago

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 didnt know this trick - error_log() but im running it via nginx not php artisan serve

gladjanus43•8mo ago

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

ericmp•8mo ago

okay, ill use log then yeah, to log it in laravel.log file so last time i used dd, that is maybe why always was null

gladjanus43•8mo ago

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

ericmp•8mo ago

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

ericmp•8mo ago

this is what im getting when i filter

Tieme•8mo ago

You are missing the use ($data) with the function ($q) thats why you are getting the error.

ericmp•8mo ago

🤦‍♂️ okay now: with this code:

Tables\Filters\SelectFilter::make('user')
    ->relationship('plays', 'name')
    ->query(function (Builder $query, array $data): Builder {
        info($data);

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

Tables\Filters\SelectFilter::make('user')
    ->relationship('plays', 'name')
    ->query(function (Builder $query, array $data): Builder {
        info($data);

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

in laravel.log:

[2024-02-22 07:19:27] local.INFO: array (
  'value' => NULL,
)  
[2024-02-22 07:19:29] local.INFO: array (
  'value' => '1',
)  
[2024-02-22 07:19:33] local.INFO: array (
  'value' => '2',
)  
[2024-02-22 07:19:35] local.INFO: array (
  'value' => '',
)

[2024-02-22 07:19:27] local.INFO: array (
  'value' => NULL,
)  
[2024-02-22 07:19:29] local.INFO: array (
  'value' => '1',
)  
[2024-02-22 07:19:33] local.INFO: array (
  'value' => '2',
)  
[2024-02-22 07:19:35] local.INFO: array (
  'value' => '',
)

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

gladjanus43•8mo ago

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

ericmp•8mo ago

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

ericmp•8mo ago

no ideas? (:

Gaming

Programming

Subquery in table filter