Lister tous les produits et leurs variantes

[Mehdib92]

Bonjour,

 

Je développez une boutique avec des articles qui ont différentes tailles et différents couleurs.

 

J'ai surchargé CategoryController.php comme ceci :

 

/* Return only the number of products */
if ($getTotal)
{
/*$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow('
SELECT COUNT(cp.`id_product`) AS total
FROM `'._DB_PREFIX_.'product` p
LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON p.`id_product` = cp.`id_product`
WHERE cp.`id_category` = '.(int)($this->id).($active ? ' AND p.`active` = 1' : '').'
'.($id_supplier ? 'AND p.id_supplier = '.(int)($id_supplier) : ''));
return isset($result) ? $result['total'] : 0;
*/
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow('
SELECT *
FROM  '._DB_PREFIX_.'product_attribute pa
LEFT JOIN '._DB_PREFIX_.'category_product cp ON pa.id_product = cp.id_product
LEFT JOIN '._DB_PREFIX_.'product_attribute_combination pac ON pa.id_product_attribute = pac.id_product_attribute
LEFT JOIN '._DB_PREFIX_.'attribute a ON pac.id_attribute = a.id_attribute
LEFT JOIN '._DB_PREFIX_.'attribute_group ag ON a.id_attribute_group = ag.id_attribute_group
LEFT JOIN '._DB_PREFIX_.'product p ON cp.id_product = p.id_product
WHERE cp.id_category = '.(int)($this->id).($active ? ' AND p.`active` = 1' : '').'
'.($id_supplier ? 'AND p.id_supplier = '.(int)($id_supplier) : '')).'
AND ag.is_color_group = 1
GROUP BY cp.id_product, a.id_attribute';
return isset($result) ? $result['total'] : 0;
break;
}


$sql = 'SELECT p.`id_product`, p.`id_supplier`, p.`id_manufacturer`, p.`id_tax_rules_group`, p.`id_category_default`, p.`id_color_default`, p.`on_sale`, p.`online_only`, p.`ean13`, p.`upc`, p.`wholesale_price`, p.`unity`, p.`unit_price_ratio`, p.`additional_shipping_cost`, p.`supplier_reference`, p.`location`, p.`width`, p.`height`, p.`depth`, p.`out_of_stock`, p.`quantity_discount`, p.`customizable`, p.`uploadable_files`, p.`text_fields`, p.`active`, p.`available_for_order`, p.`condition`, p.`show_price`, p.`indexed`, p.`cache_is_pack`, p.`cache_has_attachments`, p.`cache_default_attribute`, p.`date_add`, p.`date_upd`, pa.`id_product_attribute`, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, cl.`name` AS category_default, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY)) > 0 AS new,
  (p.`price` * IF(t.`rate`,((100 + (t.`rate`))/100),1)) AS orderprice
 FROM `'._DB_PREFIX_.'category_product` cp
 LEFT JOIN `'._DB_PREFIX_.'product` p ON p.`id_product` = cp.`id_product`
 LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)
 LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = '.(int)($id_lang).')
 LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = '.(int)($id_lang).')
 LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
 LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)($id_lang).')
 LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
										 AND tr.`id_country` = '.(int)Country::getDefaultCountryId().'
										 AND tr.`id_state` = 0)
 LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = tr.`id_tax`)
 LEFT JOIN `'._DB_PREFIX_.'tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = '.(int)($id_lang).')
 LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
 WHERE cp.`id_category` = '.(int)($this->id).($active ? ' AND p.`active` = 1' : '').'
 '.($id_supplier ? 'AND p.id_supplier = '.(int)$id_supplier : '');

 $sql .= 'ORDER BY '.(isset($orderByPrefix) ? $orderByPrefix.'.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay);
 $productsResult = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($sql);

 foreach ($productsResult as $productResult) {
  $sqlColors = "
  SELECT pai.`id_image`, ag.`id_attribute_group`, ag.`is_color_group`, agl.`name` AS group_name, agl.`public_name` AS public_group_name, a.`id_attribute`, al.`name` AS attribute_name,
  a.`color` AS attribute_color, pa.`id_product_attribute`, pa.`quantity`, pa.`price`, pa.`ecotax`, pa.`weight`, pa.`default_on`, pa.`reference`, pa.`unit_price_impact`, pa.`minimal_quantity`
  FROM `"._DB_PREFIX_."product_attribute` pa
  LEFT JOIN `"._DB_PREFIX_."product_attribute_combination` pac ON pac.`id_product_attribute` = pa.`id_product_attribute`
  LEFT JOIN `"._DB_PREFIX_."product_attribute_image` pai ON pai.`id_product_attribute` = pa.`id_product_attribute`
  LEFT JOIN `"._DB_PREFIX_."attribute` a ON a.`id_attribute` = pac.`id_attribute`
  LEFT JOIN `"._DB_PREFIX_."attribute_group` ag ON ag.`id_attribute_group` = a.`id_attribute_group`
  LEFT JOIN `"._DB_PREFIX_."attribute_lang` al ON a.`id_attribute` = al.`id_attribute`
  LEFT JOIN `"._DB_PREFIX_."attribute_group_lang` agl ON ag.`id_attribute_group` = agl.`id_attribute_group`
  WHERE pa.`id_product` =  ".$productResult['id_product']."
  AND al.`id_lang` =  ".(int)($id_lang)."
  AND agl.`id_lang` = ".(int)($id_lang)."
  AND ag.`is_color_group` = 1
  GROUP BY attribute_name
  ORDER BY agl.`public_name`, al.`name`";

  $colorsResult = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($sqlColors);

  foreach ($colorsResult as $colorResult) {
$newProduct = array_merge($productResult, $colorResult);
$productFinal[] = $newProduct;
  }
 }

 if ($random === true)
 {
  $result = array_slice($shuffle($productFinal), (int)($randomNumberProducts));
 }
 else
 {
  $result = array_slice($productFinal, (((int)($p) - 1) * (int)($n)), (int)($n));
 }

 

 

Mais j'ai un problème de pagination qui est toujours bloqué sur le nombre d'article par défaut (10) et du coup la partie pagination ne s'affiche pas malgré avoir modifié aussi la requete pour le getTotal...

 

Je sais que combiner les 2 requetes serait beaucoup plus simple mais impossible de trouver la bonne requête.

 

Quelqu'un pourrait me donner un coup de main pour une solution svp ?

 

Merci

 

EDIT : J'ai bien relu et j'ai bêtement oublié le count...