Searching with Relationship error with nested relationship

[MrPaolito]

Hi everyone, I am encountering an issue when trying to create a complete table where users can perform a quick search but
when I try to run the "search query" with a leftJoin between these tables, I get the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'owner.nome' in 'where clause' (Connection: mysql, SQL: select count(*) as aggregate from `notes` left join `vehicles` on `notes`.`vehicle_id` = `vehicles`.`id` left join `owners` on `vehicles`.`owner_id` = `owners`.`id` where ((`notes`.`data` LIKE %s% or `notes`.`created_at` LIKE %s%) or exists (select * from `vehicles` where `notes`.`vehicle_id` = `vehicles`.`id` and `marca` LIKE %s%) or exists (select * from `vehicles` where `notes`.`vehicle_id` = `vehicles`.`id` and `modello` LIKE %s%) or exists (select * from `vehicles` where `notes`.`vehicle_id` = `vehicles`.`id` and `anno` LIKE %s%) or exists (select * from `vehicles` where `notes`.`vehicle_id` = `vehicles`.`id` and `targa` LIKE %s%) or (`owner`.`nome` LIKE %s% and `owner`.`cognome` LIKE %s% and `owner`.`paese` LIKE %s%))) 

Database and Models

Here is a brief description of my database schema and models:

notes Table

• Fields: id, data, nota, vehicle_id, created_at, updated_at
• The notes table stores information related to notes and includes a foreign key reference to the vehicles table via vehicle_id.

vehicles Table

• Fields: id, targa, marca, modello, anno, owner_id
• The vehicles table contains information about vehicles and includes a foreign key reference to the owners table via owner_id.

owners Table

• Fields: id, nome, cognome, paese
• The owners table contains information about vehicle owners.

Relationships:

• notes has a foreign key vehicle_id referencing the vehicles table.
• vehicles has a foreign key owner_id referencing the owners table.

Code Implementation

datasource Method

public function datasource(): Builder
{
    return Note::query()
        ->leftJoin('vehicles', 'notes.vehicle_id', '=', 'vehicles.id')
        ->leftJoin('owners', 'vehicles.owner_id', '=', 'owners.id')
        ->select(
            'notes.id as id',
            'notes.data as data',
            'notes.nota as nota', 
            'vehicles.targa as targa',
            'vehicles.marca as marca',
            'vehicles.modello as modello',
            'vehicles.anno as anno',
            'owners.nome as nome', 
            'owners.cognome as cognome',
            'owners.paese as paese',
            'notes.created_at as created_at_formatted'
        );
}

relationSearch Method

 public function relationSearch(): array
    {

        return [
           'vehicle' => [
                'marca', 'modello', 'anno', 'targa',
                'owner' => ['nome', 'cognome', 'paese'],
                ],
            ];

    }

fields and columns Methods

public function fields(): PowerGridFields
  {

      return PowerGrid::fields()
          ->add('id')
          ->add('data_formatted', fn(Note $model) => Carbon::parse($model->data)->format('d/m/Y'))
          ->add('targa')
          ->add('marca')
          ->add('modello')
          ->add('anno')
          ->add('nome')
          ->add('cognome')
          ->add('paese')
          ->add('nota_excerpt', function (Note $note) {
              return str(e($note->nota))->words(20);
          })
          ->add('created_at_formatted', fn(Note $model) => Carbon::parse($model->created_at_formatted)->format('d/m/Y'));
  }

  public function columns(): array
  {
      return [

          Column::make('Data Intervento', 'data_formatted', 'notes.data')
              ->sortable()
              ->searchable(),
          Column::make('Targa', 'targa', 'vehicles.targa')
              ->sortable()
              ->searchable(),
          Column::make('Marca', 'marca', 'vehicles.marca')
              ->sortable()
              ->searchable(),
          Column::make('Modello', 'modello', 'vehicles.modello')
              ->sortable()
              ->searchable(),
          Column::make('Anno', 'anno', 'vehicles.anno')
              ->sortable()
              ->searchable(),
          Column::make('Nome', 'nome', 'owners.nome')
              ->sortable()
              ->searchable(),
          Column::make('Cognome', 'cognome', 'owners.cognome')
              ->sortable()
              ->searchable(),
          Column::make('Paese', 'Paese', 'owners.paese')
              ->sortable()
              ->searchable(),
          Column::make('Nota', 'nota_excerpt', 'notes.nota')
              ->sortable(),
          Column::make('Data Creazione', 'created_at_formatted', 'notes.created_at')
              ->searchable()
              ->sortable(),


      ];
  }

Issue

The query results in an error regarding the column owner.nome. The error message suggests that the column doesn't exist.

I suspect that this is an issue related to how I’m referencing the column in the query. I have checked the database structure, and it seems that the field is correctly named nome. However, it might be possible that the issue is how I’m joining referencing the owner relation or the table's settings

Additionally, I want to highlight that when I run the same query directly in the terminal, it works without any issues and returns the correct results. This suggests that the issue might be specific to how the query is constructed or executed within search powergrid component.

I noticed that the error disappears if I change owner to owners in the relationSearch array, but of course, it doesn't return any results (Example: If I specifically search for a word that is entered in the 'nome' field)

Can you help me understand where the error is hidden? Thank you very much and sorry for any possible typing errors