How to use filter on related column

Hi all.

For some reason I get really stuck on adding an (simple) filter.

In my Person model I did convert my gender field to an Enum:

 'gender' => GenderTypeField::class.

Iam connecting the Person class via an BelongsTo to multiple different models like User. In my filament User table view, the value can be easily be fetched like this:

TextColumn::make('person.gender')
->label('Geslacht')
->toggleable(isToggledHiddenByDefault: true)
->searchable()
 ->sortable(),

But now I want to make an filter on this field in my User resource.

Iam trying something like this:

SelectFilter::make('person.gender')
->label('Geslacht')
->options(GenderTypeField::class)

But this will result in: "Column not found: 1054 Unknown column 'person.gender'"

Defining a relation like this will also not work because then it will fetch the whole table:

SelectFilter::make('gender')
->relation('person','gender')
->label('Geslacht')
->options(GenderTypeField::class)

How to solve this? I did not find an example anywhere. Thanks for the help!

LaryAI

1 year ago

Level 58

To solve the issue of filtering on a related column in your Filament table, you need to ensure that the filter is correctly set up to query the related model's field. The error you're encountering, "Column not found: 1054 Unknown column 'person.gender'", suggests that the query is not correctly targeting the related table.

Here's how you can set up the filter correctly:

Ensure the Relationship is Defined: Make sure that the User model has a person relationship defined. It should look something like this:
```
public function person()
{
    return $this->belongsTo(Person::class);
}
```

Use the whereHas Method: When defining the filter, you can use the whereHas method to filter based on a related model's attribute. Here's how you can set up the SelectFilter:

use Filament\Tables\Filters\SelectFilter;

SelectFilter::make('gender')
    ->label('Geslacht')
    ->options(GenderTypeField::getOptions()) // Assuming getOptions() returns an array of options
    ->query(function ($query, $value) {
        $query->whereHas('person', function ($query) use ($value) {
            $query->where('gender', $value);
        });
    }),

Ensure Options are Correct: Make sure that GenderTypeField::getOptions() returns an array of options that match the values stored in the database. If GenderTypeField is an enum, you might need to convert it to an array of key-value pairs.

By using the whereHas method, you ensure that the filter applies to the related person model's gender field, which should resolve the "unknown column" error. This approach allows you to filter users based on the gender of their associated person record.

Please or to participate in this conversation.