[SOLVED] SQL query problem - can't handle null values, export catalog

[m.andrzejak]

Hello,

 

I have a little problem with my SQL query, I'd like export my product from directly from database, but when query encounters situation when product doesn't have any attributes CONCAT_WS statement skip NULL values and go thru and display only rows wchich products have attributes.

 

Some code:

 

Problem is in this part of query (I think):

GROUP_CONCAT(DISTINCT (CONCAT_WS (': ', GroupAttrLang.`name`, AttrLang.`name`)) ORDER BY AttrLang.`name` ASC

 

All query looks like this:

SELECT
TProduct.`reference` AS ProdReference,
ProdLang.`name` AS ProductName,
ProdLang.`description` AS ProdDesc,
ROUND((TProduct.`price`*1.23)/4.1989,2) AS ProductPrice,
CASE
WHEN COUNT(ProdAttr.`id_product`) = 0 THEN 1
ELSE (GROUP_CONCAT(DISTINCT (CONCAT_WS (': ', GroupAttrLang.`name`, AttrLang.`name`)) ORDER BY AttrLang.`name` ASC))
END AS ProdAttributes,
/*IF ((COUNT(ProdAttr.`id_product`) > 0), GROUP_CONCAT(DISTINCT (CONCAT_WS (': ', GroupAttrLang.`name`, AttrLang.`name`)) ORDER BY AttrLang.`name` ASC) , 'n/a' ) AS ProdAttributes,*/
GROUP_CONCAT(DISTINCT (CONCAT_WS (' - ', ParLang.`name`, CatLang.`name` ))) AS ProdFit
FROM `ps_product` AS TProduct
	LEFT JOIN `ps_category_product` AS CatProd ON CatProd.`id_product` = TProduct.`id_product`
	LEFT JOIN `ps_category` AS Cat ON Cat.`id_category` = CatProd.`id_category`
	LEFT JOIN `ps_category_lang` AS ParLang ON Cat.`id_parent` = ParLang.`id_category`
	LEFT JOIN `ps_product_lang` AS ProdLang ON ProdLang.`id_product` = TProduct.`id_product`
	LEFT JOIN `ps_category_lang` AS CatLang ON CatLang.`id_category` = CatProd.`id_category`
	LEFT JOIN `ps_product_attribute` AS ProdAttr ON ProdAttr.`id_product` = TProduct.`id_product`
	LEFT JOIN `ps_product_attribute_combination` AS ProdAttrComb ON ProdAttrComb.`id_product_attribute` = ProdAttr.`id_product_attribute`
	LEFT JOIN `ps_attribute` AS TAttr ON TAttr.`id_attribute` = ProdAttrComb.`id_attribute`
	LEFT JOIN `ps_attribute_group_lang` AS GroupAttrLang ON GroupAttrLang.`id_attribute_group` = TAttr.`id_attribute_group`
	LEFT JOIN `ps_attribute_lang` AS AttrLang ON AttrLang.`id_attribute` = TAttr.`id_attribute`
WHERE
	Cat.`id_parent` != 1 AND
	ProdLang.`id_lang`=1 AND
	CatLang.`id_lang` = 1 AND
	GroupAttrLang.`id_lang` = 1 AND
	AttrLang.`id_lang` = 1
		 GROUP BY TProduct.`reference` ORDER BY TProduct.`reference` ASC;

 

I tried select only one thing:

SELECT
CASE
WHEN COUNT(ProdAttr.`id_product`) > 0 THEN COUNT(ProdAttr.`id_product`)
ELSE 'n/a'
END AS ProdAttrList
FROM `ps_product` AS TProduct
LEFT JOIN `ps_product_attribute` AS ProdAttr ON ProdAttr.`id_product`=TProduct.`id_product`
	GROUP BY TProduct.`reference`;

And it work perfect (see image)

 

I tried with IFNULL and COALESCE but result was the same as above.

What I miss can anybody help me with this query?

Edited January 31, 2013 by m.andrzejak (see edit history)

[m.andrzejak]

Problem solved,

 

The problem was:

...WHERE
GroupAttrLang.`id_lang` = 1 AND
AttrLang.`id_lang` = 1...

 

The condition of id_language must be add to JOIN statement:

 

LEFT JOIN `ps_attribute_group_lang` AS GroupAttrLang ON GroupAttrLang.`id_attribute_group` = TAttr.`id_attribute_group` AND GroupAttrLang.`id_lang` = 1
LEFT JOIN `ps_attribute_lang` AS AttrLang ON AttrLang.`id_attribute` = TAttr.`id_attribute` AND AttrLang.`id_lang` = 1

 

So if you want export your product to catalog from database here is my code:

SELECT
    TProduct.`reference` AS ProdReference,
    ProdLang.`name` AS ProductName,
    ProdLang.`description` AS ProdDesc,
    ROUND((TProduct.`price`*1.23)/4.1989,2) AS ProductPrice,
    CASE
		    WHEN COUNT(ProdAttr.`id_product`) > 0 THEN (GROUP_CONCAT(DISTINCT (CONCAT_WS (': ', GroupAttrLang.`name`, AttrLang.`name`)) ORDER BY AttrLang.`name` ASC))
		    ELSE 'n/a'
    END AS ProdAttributes,

    GROUP_CONCAT(DISTINCT (CONCAT_WS (' - ', ParLang.`name`, CatLang.`name`	    ))) AS ProdFit
		    FROM `ps_product` AS TProduct
				    LEFT JOIN `ps_category_product` AS CatProd ON CatProd.`id_product` = TProduct.`id_product`
				    LEFT JOIN `ps_category` AS Cat ON Cat.`id_category` = CatProd.`id_category`
				    LEFT JOIN `ps_category_lang` AS ParLang ON Cat.`id_parent` = ParLang.`id_category`
				    LEFT JOIN `ps_product_lang` AS ProdLang ON ProdLang.`id_product` = TProduct.`id_product`
				    LEFT JOIN `ps_category_lang` AS CatLang ON CatLang.`id_category` = CatProd.`id_category`
				    LEFT JOIN `ps_product_attribute` AS ProdAttr ON ProdAttr.`id_product` = TProduct.`id_product`
				    LEFT JOIN `ps_product_attribute_combination` AS ProdAttrComb ON ProdAttrComb.`id_product_attribute` = ProdAttr.`id_product_attribute`
				    LEFT JOIN `ps_attribute` AS TAttr ON TAttr.`id_attribute` = ProdAttrComb.`id_attribute`
				    LEFT JOIN `ps_attribute_group_lang` AS GroupAttrLang ON GroupAttrLang.`id_attribute_group` = TAttr.`id_attribute_group` AND GroupAttrLang.`id_lang` = 1
		    LEFT JOIN `ps_attribute_lang` AS AttrLang ON AttrLang.`id_attribute` = TAttr.`id_attribute` AND AttrLang.`id_lang` = 1
		    WHERE
				    Cat.`id_parent` != 1 AND
				    ProdLang.`id_lang`=1 AND
				    CatLang.`id_lang` = 1
GROUP BY TProduct.`reference` ORDER BY TProduct.`reference` ASC;