Filament•3w ago

Filter not apply for the pivot table

I created a filter when I want to filter via the role of my post and this field is located in the pivot table. I tried to debug and rawSQL in logs looks on, but the application filter doesn't work. What I possibly did wrong This is code from UserResource in table() function

use Filament\Forms\Components\Select;
use Filament\Tables\Filters\Filter;
use Illuminate\Database\Query\JoinClause;
use Illuminate\Support\Facades\Log;

return Filter::make(name: 'posts.post_user.role')
    ->form(schema: [
        Select::make(name: 'post_role')
            ->options(options: PostRoleEnum::class),
    ])
    ->query(callback: function (Builder $query, array $data) use ($post): Builder {
        return $query
            ->when(
                $data['post_role'],
                callback: function (Builder $query, $role) use ($post): Builder {
                    $query2 = $query->join(table: 'post_user', first: function (JoinClause $join) use (
                        $post, $role
                    ): void {
                        $join->on(first: 'users.id', operator: '=', second: 'post_user.user_id')
                            ->where(
                                column: 'post_user.post_id',
                                operator: '=',
                                value: $post->id,
                            )->where(column: 'post_user.role', operator: '=', value: $role);
                    });

                    //value in the log is correct but why filter not refresh data, I have no idea
                    Log::info(message: 'role', context: ['sql' => $query2->toRawSql()]);

                    return $query2;
                },
            );

use Filament\Forms\Components\Select;
use Filament\Tables\Filters\Filter;
use Illuminate\Database\Query\JoinClause;
use Illuminate\Support\Facades\Log;

return Filter::make(name: 'posts.post_user.role')
    ->form(schema: [
        Select::make(name: 'post_role')
            ->options(options: PostRoleEnum::class),
    ])
    ->query(callback: function (Builder $query, array $data) use ($post): Builder {
        return $query
            ->when(
                $data['post_role'],
                callback: function (Builder $query, $role) use ($post): Builder {
                    $query2 = $query->join(table: 'post_user', first: function (JoinClause $join) use (
                        $post, $role
                    ): void {
                        $join->on(first: 'users.id', operator: '=', second: 'post_user.user_id')
                            ->where(
                                column: 'post_user.post_id',
                                operator: '=',
                                value: $post->id,
                            )->where(column: 'post_user.role', operator: '=', value: $role);
                    });

                    //value in the log is correct but why filter not refresh data, I have no idea
                    Log::info(message: 'role', context: ['sql' => $query2->toRawSql()]);

                    return $query2;
                },
            );

Have maybe someone any suggestions for me? Thanks for advance

1 Reply

Paweł WawrzyniakOP•2w ago

I used whereHas instead of join and now it works inspirations come from https://discord.com/channels/883083792112300104/1315476942451118080/1315476942451118080

Gaming

Programming

Filter not apply for the pivot table