First the sql script had to be rewritten. We didn't have time to optimize, just make it work. So the script is written accordingly. The subquerry:
SELECT x1.`id_product` FROM
(SELECT DISTINCT z.`id_product`, a.`id_product_attribute`
FROM `ps_product_attribute_combination` a
JOIN `ps_product_attribute` z on a.`id_product_attribute` =
z.`id_product_attribute` AND a.`id_attribute` = 17
JOIN `ps_stock_available` c on c.`id_product_attribute` =
a.`id_product_attribute`
WHERE c.`quantity` > 0 ) x1
JOIN
(
SELECT DISTINCT z.`id_product`, a.`id_product_attribute`
FROM `ps_product_attribute_combination` a
JOIN `ps_product_attribute` z on a.`id_product_attribute` =
z.`id_product_attribute` AND a.`id_attribute` = 37
JOIN `ps_stock_available` c on c.`id_product_attribute` =
a.`id_product_attribute`
WHERE c.`quantity` > 0 ) x2
ON x1.`id_product_attribute` = x2.`id_product_attribute`
JOIN
(
SELECT DISTINCT z.`id_product`, a.`id_product_attribute`
FROM `ps_product_attribute_combination` a
JOIN `ps_product_attribute` z on a.`id_product_attribute` =
z.`id_product_attribute` AND a.`id_attribute` = 37
JOIN `ps_stock_available` c on c.`id_product_attribute` =
a.`id_product_attribute`
WHERE c.`quantity` > 0 ) x3
ON x1.`id_product_attribute` = x3.`id_product_attribute`
The result can be joined with anything.
Then this had to be implemented in the appropiate prestashop module. This way in file
rootdir > modules > blocklayered > blocklayered.php we modified the
indexAttribute function:
public function
indexAttribute($id_product = null)
{
if (is_null($id_product))
Db::getInstance()->execute('TRUNCATE
'._DB_PREFIX_.'layered_product_attribute');
else
Db::getInstance()->execute('
DELETE
FROM '._DB_PREFIX_.'layered_product_attribute
WHERE
id_product = '.(int)$id_product
);
Db::getInstance()->execute('
INSERT
INTO `'._DB_PREFIX_.'layered_product_attribute` (`id_attribute`, `id_product`,
`id_attribute_group`, `id_shop`)
SELECT
pac.id_attribute, pa.id_product, ag.id_attribute_group,
product_attribute_shop.`id_shop`
FROM
'._DB_PREFIX_.'product_attribute pa'.
Shop::addSqlAssociation('product_attribute',
'pa').'
INNER
JOIN '._DB_PREFIX_.'product_attribute_combination pac ON
pac.id_product_attribute = pa.id_product_attribute
INNER JOIN '._DB_PREFIX_.'stock_available sa ON
(sa.id_product_attribute = pac.id_product_attribute AND sa.quantity > 0)
INNER
JOIN '._DB_PREFIX_.'attribute a ON (a.id_attribute = pac.id_attribute)
INNER
JOIN '._DB_PREFIX_.'attribute_group ag ON ag.id_attribute_group =
a.id_attribute_group
'.(is_null($id_product)
? '' : 'AND pa.id_product = '.(int)$id_product).'
GROUP
BY a.id_attribute, pa.id_product , product_attribute_shop.`id_shop`'
);
return 1;
}The getProductByFilters function also had to be modified:
case 'id_attribute_group':
$sub_queries
= array();
foreach
($filter_values as $filter_value)
{
$filter_value_array
= explode('_', $filter_value);
if
(!isset($sub_queries[$filter_value_array[0]]))
$sub_queries[$filter_value_array[0]]
= array();
$sub_queries[$filter_value_array[0]][]
= 'pac.`id_attribute` = '.(int)$filter_value_array[1];
}
$counter = 0;
$query_filters_where
.= ' AND p.id_product IN ( SELECT x1.`id_product` FROM ( ';
foreach
($sub_queries as $sub_query)
{
$counter++;
if($counter
> 1)
$query_filters_where.=
' JOIN ';
$query_filters_where
.= ' (SELECT DISTINCT pa.`id_product`, pac.`id_product_attribute`
FROM
`'._DB_PREFIX_.'product_attribute_combination` pac
INNER
JOIN `'._DB_PREFIX_.'product_attribute` pa
ON
(pa.`id_product_attribute` = pac.`id_product_attribute`) AND ('.implode(' OR ',
$sub_query).')'.
Shop::addSqlAssociation('product_attribute',
'pa').'
INNER
JOIN `'._DB_PREFIX_.'stock_available` sa
ON
(sa.id_product_attribute = pac.id_product_attribute AND sa.quantity > 0) )
x'.$counter.' ';
if($counter
> 1)
$query_filters_where.=
' ON x1.`id_product_attribute` = x'.$counter.'.id_product_attribute ';
}
$query_filters_where
.= '))';
break;And that's all. Special thanks to Gyuszi for the php code!
