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');
            }
        }
    }
}