How to use searchable() with model relationship?

[perffy]

Hi guys,

I am trying now to use your new feature searachble(), unfortunately, I am not able to make it work.

My application has a model TeamTennisOption, which has statistics and the name of each tennis player is in the model Team, so my mission is to list all tennis players and after that to search in that table.

Here is the column with the name of each player:

Column::make(__('Name'))
                ->searchable(function (Builder $query, $term) {
                    $query->join('teams', 'teams.id', '=', 'team_tennis_options.team_id')->orWhere('teams.name', 'like', '%'.$term.'%');
                })
                ->sortable(function(Builder $builder, string $direction) {
                    return $builder->orderBy(Team::select('name')->whereColumn('team_tennis_options.team_id', 'teams.id'), $direction);
                })
                ->format(function (TeamTennisOption $model) {
                    return view('backend.teams.includes.edit', ['model' => $model->team]);
                }),

When I check the laravel debugbar, it seems the code is not making the join:

select count(*) as aggregate from team_tennis_optionswheregender= 'f' andrank> 0 andplaying = 1 and (team_tennis_options.ranklike '%ash%' orteam_tennis_options.old_rank like '%ash%')

Can you please help me to solve this issue?

[nocodelab]

Hi, did you manage to solve this issue?

[perffy]

no :(

[Josefikos]

Inside query function you have to join relation table

public function query(): Builder
{
  return Article::query()->join('bdt_user', 'bdt_article.author_id', '=', 'bdt_user.id');
}

Then you can make collumn searchable like that:

Column::make('Autor', 'author_id')
  ->searchable(static function(Builder $query, $searchTerm) {
    return $query->orWhere('bdt_user.firstname', $searchTerm)->orWhere('bdt_user.lastname', $searchTerm);
  })
  ->sortable(static function(Builder $query, $direction) {
    return $query->orderBy(User::select('bdt_user.firstname')->whereColumn('bdt_article.author_id', 'bdt_user.id'), $direction);
  }),