Encore un bug blocklayered sur Prestashop, après avoir fait des siennes avec les déclinaisons de produits, voilà qu’il me casse la fonctionnalité « Visibilité : tout, catalogue, recherche, aucun » de Prestashop. La faute à ses requêtes MySQL dans blocklayered.php qui ne vérifient si le produit est activé, mais pas si sa visibilité est restreinte.
Décidément ce module de navigation à facettes blocklayered en version 1.8.9 a été codé par-dessus la jambe. La version 2.0.0 n’est pas forcément compatible avec PrestaShop 1.5.2.0 ou les thèmes utilisés.
La solution à ce grave oubli est de modifier toutes les requêtes MySQL : là où on vérifie que le produit est actif, il faut vérifier que sa visibilité est soit « tout », soit « catalogue ».
Cela fait en tout 17 modifications.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 |
--- blocklayered.php 2016-12-07 17:05:48.444915253 +0100 +++ blocklayered.php.old 2016-12-07 16:01:55.219888995 +0100 @@ -937,25 +937,25 @@ SELECT count(DISTINCT p.`id_product`) FROM '._DB_PREFIX_.'product p INNER JOIN `'._DB_PREFIX_.'product_shop` ps - ON (ps.`id_product` = p.`id_product` AND ps.`active` = 1 AND ps.`visibility` IN ("both","catalog"))'); + ON (ps.`id_product` = p.`id_product` AND ps.`active` = 1)'); else $nb_products = (int)Db::getInstance()->getValue(' SELECT count(DISTINCT p.`id_product`) FROM '._DB_PREFIX_.'product p - WHERE `active` = 1 AND `visibility` IN ("both","catalog")'); + WHERE `active` = 1'); else if (version_compare(_PS_VERSION_,'1.5','>')) $nb_products = (int)Db::getInstance()->getValue(' SELECT COUNT(DISTINCT p.`id_product`) FROM `'._DB_PREFIX_.'product` p INNER JOIN `'._DB_PREFIX_.'product_shop` ps - ON (ps.`id_product` = p.`id_product` AND ps.`active` = 1 AND ps.`visibility` IN ("both","catalog")) + ON (ps.`id_product` = p.`id_product` AND ps.`active` = 1) LEFT JOIN `'._DB_PREFIX_.'layered_price_index` psi ON (psi.id_product = p.id_product) WHERE psi.id_product IS NULL'); else $nb_products = (int)Db::getInstance()->getValue(' SELECT COUNT(DISTINCT p.`id_product`) FROM `'._DB_PREFIX_.'product` p LEFT JOIN `'._DB_PREFIX_.'layered_price_index` psi ON (psi.id_product = p.id_product) - WHERE `active` = 1 AND `visibility` IN ("both","catalog") AND psi.id_product IS NULL'); + WHERE `active` = 1 AND psi.id_product IS NULL'); $max_executiontime = @ini_get('max_execution_time'); if ($max_executiontime > 5 || $max_executiontime <= 0) @@ -1016,14 +1016,14 @@ SELECT p.`id_product` FROM `'._DB_PREFIX_.'product` p INNER JOIN `'._DB_PREFIX_.'product_shop` ps - ON (ps.`id_product` = p.`id_product` AND ps.`active` = 1 AND ps.`visibility` IN ("both","catalog")) + ON (ps.`id_product` = p.`id_product` AND ps.`active` = 1) GROUP BY p.`id_product` ORDER BY p.`id_product` LIMIT '.(int)$cursor.','.(int)$length; else $query = ' SELECT p.`id_product` FROM `'._DB_PREFIX_.'product` p - WHERE `active` = 1 AND `visibility` IN ("both","catalog") + WHERE `active` = 1 GROUP BY p.`id_product` ORDER BY p.`id_product` LIMIT '.(int)$cursor.','.(int)$length; else @@ -1032,7 +1032,7 @@ SELECT p.`id_product` FROM `'._DB_PREFIX_.'product` p INNER JOIN `'._DB_PREFIX_.'product_shop` ps - ON (ps.`id_product` = p.`id_product` AND ps.`active` = 1 AND ps.`visibility` IN ("both","catalog")) + ON (ps.`id_product` = p.`id_product` AND ps.`active` = 1) LEFT JOIN `'._DB_PREFIX_.'layered_price_index` psi ON (psi.id_product = p.id_product) WHERE psi.id_product IS NULL GROUP BY p.`id_product` @@ -1042,7 +1042,7 @@ SELECT p.`id_product` FROM `'._DB_PREFIX_.'product` p LEFT JOIN `'._DB_PREFIX_.'layered_price_index` psi ON (psi.id_product = p.id_product) - WHERE `active` = 1 AND `visibility` IN ("both","catalog") AND psi.id_product IS NULL + WHERE `active` = 1 AND psi.id_product IS NULL GROUP BY p.`id_product` ORDER BY p.`id_product` LIMIT 0,'.(int)$length; @@ -2323,7 +2323,7 @@ $alias_where = 'p'; if (version_compare(_PS_VERSION_,'1.5','>')) $alias_where = 'product_shop'; - $query_filters_where = ' AND '.$alias_where.'.`active` = 1 AND '.$alias_where.'.`visibility` IN ("both","catalog")'; + $query_filters_where = ' AND '.$alias_where.'.`active` = 1'; $query_filters_from = ''; $parent = new Category((int)$id_parent); @@ -2530,7 +2530,7 @@ 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)($cookie->id_lang).') LEFT JOIN '._DB_PREFIX_.'manufacturer m ON (m.id_manufacturer = p.id_manufacturer) - WHERE '.$alias_where.'.`active` = 1 AND '.$alias_where.'.`visibility` IN ("both","catalog") AND + WHERE '.$alias_where.'.`active` = 1 AND '.(Configuration::get('PS_LAYERED_FULL_TREE') ? 'c.nleft >= '.(int)$parent->nleft.' AND c.nright <= '.(int)$parent->nright : 'c.id_category = '.(int)$id_parent).' AND c.active = 1 @@ -2632,10 +2632,10 @@ { $sql_query['join'] .= 'LEFT JOIN `'._DB_PREFIX_.'stock_available` sa ON (sa.id_product = p.id_product AND sa.id_shop = '.(int)$this->context->shop->id.') '; - $sql_query['where'] = 'WHERE product_shop.`active` = 1 AND product_shop.`visibility` IN ("both","catalog") '; + $sql_query['where'] = 'WHERE product_shop.`active` = 1 '; } else - $sql_query['where'] = 'WHERE p.`active` = 1 AND p.`visibility` IN ("both","catalog") '; + $sql_query['where'] = 'WHERE p.`active` = 1 '; $sql_query['group'] = ' GROUP BY p.id_product '; break; @@ -2650,7 +2650,7 @@ '.(Configuration::get('PS_LAYERED_FULL_TREE') ? 'c.nleft >= '.(int)$parent->nleft.' AND c.nright <= '.(int)$parent->nright : 'c.id_category = '.(int)$id_parent).' AND c.active = 1 - AND '.$alias.'.active = 1 AND '.$alias.'.visibility IN ("both","catalog") '; + AND '.$alias.'.active = 1'; $sql_query['group'] = ' GROUP BY p.id_manufacturer '; if (!Configuration::get('PS_LAYERED_HIDE_0_VALUES')) @@ -2667,7 +2667,7 @@ WHERE '.(Configuration::get('PS_LAYERED_FULL_TREE') ? 'c.nleft >= '.(int)$parent->nleft.' AND c.nright <= '.(int)$parent->nright : 'c.id_category = '.(int)$id_parent).' AND c.active = 1 - AND '.$alias.'.active = 1 AND '.$alias.'.visibility IN ("both","catalog") + AND '.$alias.'.active = 1 GROUP BY p.id_manufacturer'; } @@ -2698,7 +2698,7 @@ $sql_query['where'] = 'WHERE a.id_attribute_group = '.(int)$filter['id_value']; if (version_compare(_PS_VERSION_,'1.5','>')) $sql_query['where'] .= ' AND lpa.`id_shop` = '.(int)Context::getContext()->shop->id; - $sql_query['where'] .= ' AND '.$alias.'.active = 1 AND '.$alias.'.visibility IN ("both","catalog") AND p.id_product IN ( + $sql_query['where'] .= ' AND '.$alias.'.active = 1 AND p.id_product IN ( SELECT id_product FROM '._DB_PREFIX_.'category_product cp INNER JOIN '._DB_PREFIX_.'category c ON (c.id_category = cp.id_category AND @@ -2735,7 +2735,7 @@ LEFT JOIN '._DB_PREFIX_.'layered_indexable_attribute_lang_value lial ON (lial.id_attribute = lpa.id_attribute AND lial.id_lang = '.$id_lang.') - WHERE '.$alias.'.active = 1 AND '.$alias.'.visibility IN ("both","catalog") AND a.id_attribute_group = '.(int)$filter['id_value'].' + WHERE '.$alias.'.active = 1 AND a.id_attribute_group = '.(int)$filter['id_value'].' '.(version_compare(_PS_VERSION_,'1.5','>') ? 'AND lpa.`id_shop` = '.(int)Context::getContext()->shop->id : '').' GROUP BY lpa.id_attribute @@ -2757,7 +2757,7 @@ ON (lifl.id_feature = fp.id_feature AND lifl.id_lang = '.$id_lang.') LEFT JOIN '._DB_PREFIX_.'layered_indexable_feature_value_lang_value lifvl ON (lifvl.id_feature_value = fp.id_feature_value AND lifvl.id_lang = '.$id_lang.') '; - $sql_query['where'] = 'WHERE '.$alias.'.`active` = 1 AND '.$alias.'.`visibility` IN ("both","catalog") AND fp.id_feature = '.(int)$filter['id_value'].' + $sql_query['where'] = 'WHERE '.$alias.'.`active` = 1 AND fp.id_feature = '.(int)$filter['id_value'].' AND p.id_product IN ( SELECT id_product FROM '._DB_PREFIX_.'category_product cp @@ -2784,7 +2784,7 @@ ON (lifl.id_feature = fp.id_feature AND lifl.id_lang = '.$id_lang.') LEFT JOIN '._DB_PREFIX_.'layered_indexable_feature_value_lang_value lifvl ON (lifvl.id_feature_value = fp.id_feature_value AND lifvl.id_lang = '.$id_lang.') - WHERE '.$alias.'.`active` = 1 AND '.$alias.'.`visibility` IN ("both","catalog") AND fp.id_feature = '.(int)$filter['id_value'].' + WHERE '.$alias.'.`active` = 1 AND fp.id_feature = '.(int)$filter['id_value'].' GROUP BY fv.id_feature_value'; } @@ -2801,7 +2801,7 @@ FROM '._DB_PREFIX_.'category_product cp LEFT JOIN '._DB_PREFIX_.'product p ON (p.id_product = cp.id_product) '; $sql_query['where'] = ' - WHERE cp.id_category = c.id_category AND '.$alias.'.active = 1 AND '.$alias.'.visibility IN ("both","catalog") '; + WHERE cp.id_category = c.id_category AND '.$alias.'.active = 1 '; $sql_query['group'] = ') count_products FROM '._DB_PREFIX_.'category c LEFT JOIN '._DB_PREFIX_.'category_lang cl ON (cl.id_category = c.id_category AND cl.id_lang = '.$id_lang.') @@ -4013,7 +4013,7 @@ LEFT JOIN '._DB_PREFIX_.'category c ON (c.id_category = cp.id_category) WHERE c.active = 1'. (count($categories_ids) ? ' AND cp.id_category IN ('.implode(',', $categories_ids).')' : '').' - AND '.$alias.'.active = 1 AND '.$alias.'.visibility IN ("both","catalog")'. + AND '.$alias.'.active = 1'. (count($products_ids) ? ' AND p.id_product IN ('.implode(',', $products_ids).')' : '')); $attribute_groups_by_id = array(); @@ -4029,7 +4029,7 @@ LEFT JOIN '._DB_PREFIX_.'category_product cp ON (cp.id_product = p.id_product) LEFT JOIN '._DB_PREFIX_.'category c ON (c.id_category = cp.id_category) WHERE (fv.custom IS NULL OR fv.custom = 0) AND c.active = 1'.(count($categories_ids) ? ' AND cp.id_category IN ('.implode(',', $categories_ids).')' : '').' - AND '.$alias.'.active = 1 AND '.$alias.'.visibility IN ("both","catalog")'.(count($products_ids) ? ' AND p.id_product IN ('.implode(',', $products_ids).')' : '')); + AND '.$alias.'.active = 1'.(count($products_ids) ? ' AND p.id_product IN ('.implode(',', $products_ids).')' : '')); $features_by_id = array(); while ($row = $db->nextRow($features)) @@ -4049,7 +4049,7 @@ '.$join_product.$join_product_attribute.' LEFT JOIN '._DB_PREFIX_.'product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute) WHERE c.active = 1'.(count($categories_ids) ? ' AND cp.id_category IN ('.implode(',', $categories_ids).')' : ''). - ' AND '.$alias.'.active = 1 AND '.$alias.'.visibility IN ("both","catalog")'. + ' AND '.$alias.'.active = 1'. (count($products_ids) ? ' AND p.id_product IN ('.implode(',', $products_ids).')' : ''). ' AND (fv.custom IS NULL OR fv.custom = 0) GROUP BY p.id_product'); |
On retrouve cette modification ici sur GitHub. Si j’avais su, je n’aurais pas cherché !
