Make list searchable for a 2 deeper relationship

191 views Asked by At

OctoberCMS : V 2.1.17

I am building a backend list for my plugin on OctoberCMS v2. My list controller displays a list with many columns related to many relations for this model. One of those columns displays a value from a 2 deeper relationship.

After some searches on google, i've found this solution that i tried : Making a deep related field searchable in OctoberCMS

Here is my listExtendQuery function in my controller :

public function listExtendQuery($query, $definition)
    {
        if($definition == "suivis"){
            $dossierProductsTableName = (new DossierProducts)->getTable();
            $dossierTableName = (new Dossier)->getTable();
            $etablissementTableName = (new Etablissement)->getTable();
            
            $query->addSelect($etablissementTableName . '.name as etablissement_name');
            $query->leftJoin($dossierTableName, $dossierTableName . '.id', '=', $dossierProductsTableName . '.dossier_id');
            $query->leftJoin($etablissementTableName, $etablissementTableName . '.id', '=', $dossierTableName . '.etablissement_id');
            
    
        }
    }

and here is the error message i always get when i enter text in the search input :

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'lucaspalomba_packsinterieurs_dossiers_products.etablissement_name' in 'where clause' (SQL: select `lucaspalomba_packsinterieurs_dossiers_products`.*, (select concat(id, " - ", name) from `lucaspalomba_packsinterieurs_pieces` where `lucaspalomba_packsinterieurs_dossiers_products`.`piece_id` = `lucaspalomba_packsinterieurs_pieces`.`id`) as `piece_id`, (select concat(id, " - ", name) from `lucaspalomba_packsinterieurs_categories` where `lucaspalomba_packsinterieurs_dossiers_products`.`categorie_id` = `lucaspalomba_packsinterieurs_categories`.`id`) as `category_id`, `lucaspalomba_packsinterieurs_etablissements`.`name` as `etablissement_name` from `lucaspalomba_packsinterieurs_dossiers_products` left join `lucaspalomba_packsinterieurs_dossiers` on `lucaspalomba_packsinterieurs_dossiers`.`id` = `lucaspalomba_packsinterieurs_dossiers_products`.`dossier_id` left join `lucaspalomba_packsinterieurs_etablissements` on `lucaspalomba_packsinterieurs_etablissements`.`id` = `lucaspalomba_packsinterieurs_dossiers`.`etablissement_id` where (((lower(lucaspalomba_packsinterieurs_dossiers_products.etablissement_name) LIKE %sil%)) or exists (select * from `lucaspalomba_packsinterieurs_pieces` where `lucaspalomba_packsinterieurs_dossiers_products`.`piece_id` = `lucaspalomba_packsinterieurs_pieces`.`id` and ((lower(concat(id, " - ", name)) LIKE %sil%))) or exists (select * from `lucaspalomba_packsinterieurs_categories` where `lucaspalomba_packsinterieurs_dossiers_products`.`categorie_id` = `lucaspalomba_packsinterieurs_categories`.`id` and ((lower(concat(id, " - ", name)) LIKE %sil%)))) order by `id` asc)" on line 669 of /home/siqzrpd/interiors/vendor/laravel/framework/src/Illuminate/Database/Connection.php

i didn't found any solution. ANd it's why i'm asking you if someone knows why this error is displaying ?

thanks a lot, best.

Lucas

0

There are 0 answers