No column found

[Joserepi]

Hello everyone,

I've created a new column to ps_category table, but I can't retrieve data from it, returns an error -->

Fatal error: Uncaught Unknown column 'pc.cn_eight' in 'field list

This column exists in category table.

I've cleared the cache, but the error still persist.

Plase, could you help me? Same select query in MySQL client (heidi) works without problems.

 

Thanks in advance

[Ress]

Can you post the code to see exactly what you are doing, to make sure we understand correctly.?

[Joserepi]

Hello Ress,

 

Thanks for your response.

select 
                        pcp.id_category, 
                        SUM(pod.unit_price_tax_excl) as total_wt, 
                        count(*) as quantity, 
                        SUM(pgl.weight) as total_weight, 
                        pcg.vat as vat,
                        pc.cn_eight as cn_eight
                    from 
                        ps_order_detail pod 
                        left join ps_category_product pcp on pcp.id_product = pod.product_id
                        left join ps_category pc on pc.id_category = pcp.id_category 
                        left join ps_order_invoice poi on poi.id_order = pod.id_order 
                        left join ps_orders po on po.id_order = pod.id_order 
                        left join ps_gls_label pgl on pgl.id_order = pod.id_order 
                        left join ps_customer_gcvatncg pcg on pcg.id_customer = po.id_customer 
                    WHERE 
                        po.date_add BETWEEN "' . $fromDate . ' 00:00:00" 
                        AND "' . $toDate . ' 00:00:00"
                        AND po.current_state in (2, 3, 4, 5) 
                        AND po.id_order not IN (
                            select 
                            poh.id_order 
                            from 
                            ps_order_history poh 
                            where 
                            poh.id_order_state = 7
                        ) 
                        AND (
                            pcg.vat is not null 
                            and pcg.vat != "" 
                            and pcg.vat != " "
                        ) 
                        group by 
                        po.id_customer, 
                        pcp.id_category';
                        
                        var_dump($request);
                        $result = $db
            ->executeS($request);
        $total[] = ['orders_vat' => $result];

The query works perfectly in MySQL. It's necessary to indicate new columns in some Prestashop core file?

Thanks.

Regards.

[Ress]

If the column is in the database, you do not have to point anywhere the new column in prestashop, because you are doing a sql directly to the database. Could you still post the exact php code? How did you write the sql query? Because I think it's just a matter of syntax.

[Joserepi]

Exactly! It's the first time that I've this error...

Sure! this is my code:

private function getDataFromDB(string $fromDate, string $toDate)
    {
        $db = DbCore::getInstance();

        $total = array();

        //Orders from clients with NIF/VAT number

        $request = 'select 
                        pcp.id_category, 
                        SUM(pod.unit_price_tax_excl) as total_wt, 
                        count(*) as quantity, 
                        SUM(pgl.weight) as total_weight, 
                        pcg.vat as vat,
                        pc.code_cn as code_cn
                    from 
                        ps_order_detail pod 
                        left join ps_category_product pcp on pcp.id_product = pod.product_id
                        left join ps_category pc on pc.id_category = pcp.id_category 
                        left join ps_order_invoice poi on poi.id_order = pod.id_order 
                        left join ps_orders po on po.id_order = pod.id_order 
                        left join ps_gls_label pgl on pgl.id_order = pod.id_order 
                        left join ps_customer_gcvatncg pcg on pcg.id_customer = po.id_customer 
                    WHERE 
                        po.date_add BETWEEN "' . $fromDate . ' 00:00:00" 
                        AND "' . $toDate . ' 23:59:59"
                        AND po.current_state in (2, 3, 4, 5) 
                        AND po.id_order not IN (
                            select 
                            poh.id_order 
                            from 
                            ps_order_history poh 
                            where 
                            poh.id_order_state = 7
                        ) 
                        AND (
                            pcg.vat is not null 
                            and pcg.vat != "" 
                            and pcg.vat != " "
                        ) 
                        group by 
                        po.id_customer, 
                        pcp.id_category';

        $result = $db
            ->executeS($request);

        $total[] = ['orders_vat' => $result];
     
		return $total;
}

Thanks.

Edited March 14, 2022 by Joserepi (see edit history)

[Joserepi]

Solved, it's just an issue related with the cache.

 

Thanks.