SQLSTATE[42S22]: Column not found: 1054 Unknown column 'c.id_customer' in 'on clause'

[zieddams]

Hi, I have tested this module and i'm getting an error .
in back-office on visiting the client page (single client) this error appear
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'c.id_customer' in 'on clause'
Any Help please ?

[taoufiqaitali]

@zieddams
what version of prestashop you have??
can you check if module installed without errors

[zieddams]

@taoufiqaitali
i am using 1.7.8.3 version , and the module installation done successfully
the module work just fine i see the the phone field and in the register page (FrontOffice) and in the list of users (BackOffice)
but when i visit i single row (user) of the list in the BackOffice it break with this error "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'c.id_customer' in 'on clause' "

[taoufiqaitali]

module is not tested with this version
try unhook module from position hookActionCustomerGridQueryBuilderModifier
or edit file and fix the query, it exist in this hook

[zieddams]

ok , thank you for time , i will give you feedback here if i solve it.

[zieddams]

Hi @taoufiqaitali , I solved the problem here is my solution

public function hookActionCustomerGridQueryBuilderModifier(array $params)
{
    $searchQueryBuilder = $params['search_query_builder'];
    $customColumnPrefix = "customer";
    $tables = $searchQueryBuilder->getQueryParts()['from'];
    foreach ($tables as $table) {
        if($table['table'] == pSQL(_DB_PREFIX_) . 'customer'){
           $customColumnPrefix="c";
        }
    }        
    $searchCriteria = $params['search_criteria'];
    $searchQueryBuilder->addSelect(
        'IF(wcm.`phone` IS NULL,0,wcm.`phone`) AS `phone`'
    );
    $searchQueryBuilder->leftJoin(
        'c',
        '`' . pSQL(_DB_PREFIX_) . 'customer`',
        'wcm',
        'wcm.`id_customer` = '.$customColumnPrefix.'.`id_customer`'
    );


    if ('phone' === $searchCriteria->getOrderBy()) {
        $searchQueryBuilder->orderBy('wcm.`phone`', $searchCriteria->getOrderWay());
    }

    foreach ($searchCriteria->getFilters() as $filterName => $filterValue) {
        if ('phone' === $filterName) {
            $searchQueryBuilder->andWhere('wcm.`phone` = :phone');
            $searchQueryBuilder->setParameter('phone', $filterValue);

            if (!$filterValue) {
                $searchQueryBuilder->orWhere('wcm.`phone` IS NULL');
            }
        }
    }
}