Afficher un attribut dans product-list

[Shonen]

Bonjour,

 

J'ai manuellement créé un système de datepicker comme attribut pour le module blocklayered.

Ainsi l'internaute peut choisir une date, et trier ses produits par date.

 

Maintenant j'aimerais récupérer les attributs du produits (ici la date) pour l'afficher dans la liste des produits, et que cet affichage se mette à jour lorsqu'on clique sur le calendrier que j'ai mis en place.

 

Hors j'ai un affichage différent sur mon site en production et mon site en développement (strictement même base de donnée).

 

Voici le code qui m'importe, et je ne dois pas être assez calé en SQL je pense.

 

override/modules/blocklayered/blocklayered.php

function getProductByFilters

$date_chosen = new DateTime();
$date_chosen = $date_chosen->format("Y-m-d");

switch ($key)
{
  case 'id_attribute_date' :
  foreach ($filter_values as $filter_value){
    $query_filters_where .= ' AND p.id_product IN (SELECT pa.`id_product`
    FROM `'._DB_PREFIX_.'product_attribute_combination` pac
    LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa
    ON (pa.`id_product_attribute` = pac.`id_product_attribute`)'.
    Shop::addSqlAssociation('product_attribute', 'pa').'
    LEFT JOIN `'._DB_PREFIX_.'attribute` a
    ON (a.`id_attribute` = pac.`id_attribute`)'.
    Shop::addSqlAssociation('attribute', 'a').'
    WHERE a.date >= "'.$filter_value.'") ';
    $date_chosen = $filter_value;
  }
break;
}

$this->products = Db::getInstance()->executeS('
SELECT
  p.*,
  '.($alias_where == 'p' ? '' : 'product_shop.*,' ).'
  '.$alias_where.'.id_category_default,
  pl.*,
  image_shop.`id_image` id_image,
  il.legend,
  a.date,
  m.name manufacturer_name,
  '.(Combination::isFeatureActive() ? 'product_attribute_shop.id_product_attribute id_product_attribute,' : '').'
  DATEDIFF('.$alias_where.'.`date_add`, DATE_SUB("'.date('Y-m-d').' 00:00:00", INTERVAL '.(int)$nb_day_new_product.' DAY)) > 0 AS new,
  stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity'.(Combination::isFeatureActive() ? ', product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity' : '').'
FROM '._DB_PREFIX_.'cat_filter_restriction cp
LEFT JOIN `'._DB_PREFIX_.'product` p ON p.`id_product` = cp.`id_product`
'.Shop::addSqlAssociation('product', 'p').
(Combination::isFeatureActive() ?
' LEFT JOIN `'._DB_PREFIX_.'product_attribute_shop` product_attribute_shop
  ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop='.(int)$context->shop->id.')':'').'
LEFT JOIN '._DB_PREFIX_.'product_attribute pa ON (pa.id_product = p.id_product)
LEFT JOIN '._DB_PREFIX_.'product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN '._DB_PREFIX_.'attribute a ON (pac.id_attribute = a.id_attribute)
LEFT JOIN '._DB_PREFIX_.'product_lang pl ON (pl.id_product = p.id_product'.Shop::addSqlRestrictionOnLang('pl').' AND pl.id_lang = '.(int)$cookie->id_lang.')
LEFT JOIN `'._DB_PREFIX_.'image_shop` image_shop
  ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop='.(int)$context->shop->id.')
LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$cookie->id_lang.')
LEFT JOIN '._DB_PREFIX_.'manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
'.Product::sqlStock('p', 0).'
WHERE '.$alias_where.'.`active` = 1 AND '.$alias_where.'.`visibility` IN ("both", "catalog")
AND a.date >= "'.$date_chosen.'"
GROUP BY p.id_product
ORDER BY a.date, '.Tools::getProductsOrder('by', Tools::getValue('orderby'), true).' '.Tools::getProductsOrder('way', Tools::getValue('orderway')).' , cp.id_product'.
' LIMIT '.(((int)$this->page - 1) * $n.','.$n), true, false);

Voici à quoi ressemblait le code avant que je n'intervienne :

$this->products = Db::getInstance()->executeS('
SELECT
  p.*,
  '.($alias_where == 'p' ? '' : 'product_shop.*,' ).'
  '.$alias_where.'.id_category_default,
  pl.*,
  image_shop.`id_image` id_image,
  il.legend,
  m.name manufacturer_name,
  '.(Combination::isFeatureActive() ? 'product_attribute_shop.id_product_attribute id_product_attribute,' : '').'
  DATEDIFF('.$alias_where.'.`date_add`, DATE_SUB("'.date('Y-m-d').' 00:00:00", INTERVAL '.(int)$nb_day_new_product.' DAY)) > 0 AS new,
  stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity'.(Combination::isFeatureActive() ? ', product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity' : '').'
FROM '._DB_PREFIX_.'cat_filter_restriction cp
LEFT JOIN `'._DB_PREFIX_.'product` p ON p.`id_product` = cp.`id_product`
'.Shop::addSqlAssociation('product', 'p').
(Combination::isFeatureActive() ?
' LEFT JOIN `'._DB_PREFIX_.'product_attribute_shop` product_attribute_shop
  ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop='.(int)$context->shop->id.')':'').'
LEFT JOIN '._DB_PREFIX_.'product_lang pl ON (pl.id_product = p.id_product'.Shop::addSqlRestrictionOnLang('pl').' AND pl.id_lang = '.(int)$cookie->id_lang.')
LEFT JOIN `'._DB_PREFIX_.'image_shop` image_shop
  ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop='.(int)$context->shop->id.')
LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$cookie->id_lang.')
LEFT JOIN '._DB_PREFIX_.'manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
'.Product::sqlStock('p', 0).'
WHERE '.$alias_where.'.`active` = 1 AND '.$alias_where.'.`visibility` IN ("both", "catalog")
ORDER BY '.Tools::getProductsOrder('by', Tools::getValue('orderby'), true).' '.Tools::getProductsOrder('way', Tools::getValue('orderway')).' , cp.id_product'.
' LIMIT '.(((int)$this->page - 1) * $n.','.$n), true, false);

Je ne sais pas si je suis assez clair, mais en gros au résultat je perd certaines dates, par exemple un produit est proposé à la date du 21 Octobre et du 2 Novembre, et avec ce code il ne me propose que le 2 Novembre (sur le product-list.tpl).

 

J'affiche ensuite simplement sur mon product-list.

<p>{$product.date|date_format:"%A %e %B %Y"}</p>

Edited October 16, 2017 by Shonen (see edit history)

[Shonen]

Comme je m'en doutais, j'étais juste mauvais en SQL. Un simple ajout de MIN(a.date) a suffit !

$this->products = Db::getInstance()->executeS('
SELECT
  p.*,
  '.($alias_where == 'p' ? '' : 'product_shop.*,' ).'
  '.$alias_where.'.id_category_default,
  pl.*,
  image_shop.`id_image` id_image,
  il.legend,
  MIN(a.date) as date,
  m.name manufacturer_name,
  '.(Combination::isFeatureActive() ? 'product_attribute_shop.id_product_attribute id_product_attribute,' : '').'
  DATEDIFF('.$alias_where.'.`date_add`, DATE_SUB("'.date('Y-m-d').' 00:00:00", INTERVAL '.(int)$nb_day_new_product.' DAY)) > 0 AS new,
  stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity'.(Combination::isFeatureActive() ? ', product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity' : '').'
FROM '._DB_PREFIX_.'cat_filter_restriction cp
LEFT JOIN `'._DB_PREFIX_.'product` p ON p.`id_product` = cp.`id_product`
'.Shop::addSqlAssociation('product', 'p').
(Combination::isFeatureActive() ?
' LEFT JOIN `'._DB_PREFIX_.'product_attribute_shop` product_attribute_shop
  ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop='.(int)$context->shop->id.')':'').'
LEFT JOIN '._DB_PREFIX_.'product_attribute pa ON (pa.id_product = p.id_product)
LEFT JOIN '._DB_PREFIX_.'product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN '._DB_PREFIX_.'attribute a ON (pac.id_attribute = a.id_attribute)
LEFT JOIN '._DB_PREFIX_.'product_lang pl ON (pl.id_product = p.id_product'.Shop::addSqlRestrictionOnLang('pl').' AND pl.id_lang = '.(int)$cookie->id_lang.')
LEFT JOIN `'._DB_PREFIX_.'image_shop` image_shop
  ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop='.(int)$context->shop->id.')
LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$cookie->id_lang.')
LEFT JOIN '._DB_PREFIX_.'manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
'.Product::sqlStock('p', 0).'
WHERE '.$alias_where.'.`active` = 1 AND '.$alias_where.'.`visibility` IN ("both", "catalog")
AND date >= "'.$date_chosen.'"
GROUP BY p.id_product
ORDER BY date, '.Tools::getProductsOrder('by', Tools::getValue('orderby'), true).' '.Tools::getProductsOrder('way', Tools::getValue('orderway')).' , cp.id_product'.
' LIMIT '.(((int)$this->page - 1) * $n.','.$n), true, false);

Comment passer des h² sur un problème 

Have fun !