Customize the search query in the TextColumn searchable

I have created a page and on it I have the following code that shows a table with the name of the enrolled student according to the school cycle determined by "request('schoolCycle')".

public static function table(Table $table): Table
    {
        return $table
            ->query(
                Enrollment::query()
                    ->with(['student','schoolCycle','schoolGroup'])
                    ->where('school_cycle_id', request('schoolCycle'))
            )
            ->columns([
                TextColumn::make('nameStudent')
                    ->label('Nombre')
                    ->getStateUsing( function(Enrollment $enrollment) {
                        return $enrollment->student->nameStudent;
                    })
                    ->searchable(query: function (Builder $query, string $search): Builder {
                        return Enrollment::query()
                            ->with(['student','schoolCycle','schoolGroup'])
                            ->where(function (Builder $query) use ($search){
                                $query->whereHas( 'student', function (Builder $query) use ($search){
                                    return $query->where('given_name', 'like', '%'.$search.'%');
                                });
                            });
                    }),
                TextColumn::make('surnameStudent')
                    ->label('Apellido')
                    ->getStateUsing( function(Enrollment $enrollment) {
                        return $enrollment->student->surnameStudent;
                    }),
                TextColumn::make('school_cycle')
                    ->label('Ciclo')
                    ->getStateUsing( function(Enrollment $enrollment) {
                        return $enrollment->schoolCycle->name;
                    }),
                TextColumn::make('school_group')
                    ->label('Grupo')
                    ->getStateUsing( function(Enrollment $enrollment) {
                        return $enrollment->schoolGroup->name;
                    })
            ]);
    }

The query to load the table is this

Enrollment::query()
    ->with(['student','schoolCycle','schoolGroup'])
    ->where('school_cycle_id', request('schoolCycle'))

The table loads with the relationships:

->with(['student','schoolCycle','schoolGroup'])

I want to search using the student's name, but I can't get filament to return the student's name. Enrollments are required to belong to the cycle marked by request('schoolCycle') when searching for the student's name.

Thanks for your help

LaryAI

1 year ago

Level 58

To customize the search query in the TextColumn to search for a student's name while ensuring that the enrollments belong to the cycle marked by request('schoolCycle'), you need to adjust the searchable method to include the school_cycle_id condition in the search query.

Here's the updated code:

public static function table(Table $table): Table
{
    return $table
        ->query(
            Enrollment::query()
                ->with(['student', 'schoolCycle', 'schoolGroup'])
                ->where('school_cycle_id', request('schoolCycle'))
        )
        ->columns([
            TextColumn::make('nameStudent')
                ->label('Nombre')
                ->getStateUsing(function (Enrollment $enrollment) {
                    return $enrollment->student->nameStudent;
                })
                ->searchable(query: function (Builder $query, string $search): Builder {
                    return $query
                        ->where('school_cycle_id', request('schoolCycle'))
                        ->whereHas('student', function (Builder $query) use ($search) {
                            $query->where('given_name', 'like', '%' . $search . '%');
                        });
                }),
            TextColumn::make('surnameStudent')
                ->label('Apellido')
                ->getStateUsing(function (Enrollment $enrollment) {
                    return $enrollment->student->surnameStudent;
                }),
            TextColumn::make('school_cycle')
                ->label('Ciclo')
                ->getStateUsing(function (Enrollment $enrollment) {
                    return $enrollment->schoolCycle->name;
                }),
            TextColumn::make('school_group')
                ->label('Grupo')
                ->getStateUsing(function (Enrollment $enrollment) {
                    return $enrollment->schoolGroup->name;
                })
        ]);
}

In this solution, the searchable method for the nameStudent column is modified to include the school_cycle_id condition. This ensures that the search is performed within the context of the specified school cycle. The whereHas method is used to filter enrollments based on the student's name, ensuring that the search term is matched against the given_name field of the related student model.

Please or to participate in this conversation.