How to get results with where condition in Filament Resource

I am using Laravel Filament (3.0) and made a Resource for Court model which works fine.

I have a role_id field in profiles table relationship with id in role table and court_id field in profile table relationship with id in court table.

I need to dislplay the profile.name attibute in Judge column on Court Resource where court_id = row court id and role_id = 4

Can i add a where condition in filament to get this?

CourtResource table

return $table
      ->columns([
        Tables\Columns\TextColumn::make('name')
          ->label('Court Name')
          ->searchable(),
        Tables\Columns\TextColumn::make('profile.fullname')
          ->label('Judge')
          ->sortable(),
      ])

CourtModel

 public function profile()
  {
    return $this->belongsTo(Profile::class);
  }

ProfileModel

public function court()
  {
    return $this->hasOne(Court::class);
  }

  public function role()
  {
    return $this->hasOne(Role::class);
  }

RoleModel

  public function profile()
  {
    return $this->belongsToMany(Profile::class);
  }

First, ensure your relationships are set up correctly in your models.

Profiles probably belong to a Court and a Role, not the other way around.

ProfileModel

public function court()
{
    return $this->belongsTo(Court::class, 'court_id');
}

public function role()
{
    return $this->belongsTo(Role::class, 'role_id');
}

CourtModel

public function profiles()
{
    return $this->hasMany(Profile::class, 'court_id');
}

RoleModel

public function profiles()
{
    return $this->hasMany(Profile::class, 'role_id');
}

Now, to display the profile.name where role_id = 4 and court_id matches the row's court ID, you will likely need to create a custom column that computes the value on the fly. You can use a custom accessor on the Court model or a computed column directly in the Filament resource.

Add a method to the Court model to get the judge's name:

public function getJudgeNameAttribute()
{
    $judge = $this->profiles()->where('role_id', 4)->first();
    return $judge ? $judge->name : 'No Judge Assigned';
}

Then, use this accessor in your Filament table:

return $table
    ->columns([
        Tables\Columns\TextColumn::make('name')
            ->label('Court Name')
            ->searchable(),
        Tables\Columns\TextColumn::make('judge_name')
            ->label('Judge')
            ->sortable(),
    ]);

Or you can also do,

Define a computed column directly in the Filament resource without modifying the model:

return $table
    ->columns([
        Tables\Columns\TextColumn::make('name')
            ->label('Court Name')
            ->searchable(),
        Tables\Columns\TextColumn::make('profiles')
            ->getStateUsing(fn ($record) => $record->profiles()->where('role_id', 4)->first()->name ?? 'No Judge Assigned')
            ->label('Judge')
            ->sortable(),
    ]);