[SOLVED] SQL error in the backoffice

[Appolline]

Hi

 

My SQL request works perfectly in Phpmyadmin but not in the prestashop backoffice.

 

Any idea ?

 

This is my SQL request, thank you very much for your help.

SELECT 
    p.id_product,
    p.id_category_default,
    GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ";") as categories,
    CASE WHEN pa.reference IS NULL THEN p.reference ELSE pa.reference END as reference,
    pl.name, 
CASE WHEN pa.price IS NULL THEN p.price ELSE p.price + pa.price END as price,

p.wholesale_price,	
    pq.quantity
FROM ps_product p
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) 
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product AND (pa.id_product_attribute IS NULL OR pa.id_product_attribute = pq.id_product_attribute) ) 
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute)
LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute) AND pal.id_lang = 1
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
WHERE pl.id_lang = 1
GROUP BY reference
ORDER BY p.id_product, pac.id_attribute

Edited June 20, 2016 by Appolline (see edit history)

[coeos.pro]

perhaps GROUP BY reference

 

p.reference or pa.reference ?

[bellini13]

for the future, when people say "things don't work", it is usually helpful to provide a meaning of what doesn't work.  in this case, why not provide the mysql error you were receiving...

[Appolline]

@bellin13 because I thought it was easier for you to copy and paste this request in the prestashop backoffice and see directly the error message than understand my bad translation of the error  

 

Google translates like that : "When several tables are used , each attribute must be referenced with one of these tables."

 

 

@coeos.pro did you try it ? For me it does not work anymore 

 

thank you for your help but I really need to use the backoffice for this 

Edited June 19, 2016 by Appolline (see edit history)

[bellini13]

the above query runs for me in phpmyadmin.  It also runs properly from the back office SQL Manager page.

[coeos.pro]

yes i try your request in the back office with Big Data :
 

[Appolline]

thank you very much for your free module, my request works.

 

So there is a last issue, the excel export doesn't work very well, the columns are not good. arf...

Actually, it's not the same table than the "preview" 

The preview is prefect, it'is exactly the same as the export for MS Excel from Phpmyadmin.

 

Thank you

[coeos.pro]

change SEPARATOR ";" by SEPARATOR "|"

[Appolline]

THANK YOU !

 

all works perfectly now. Good job