Prestashop - Cart rules query problem [SOLVED]
There is a severe problem in Prestashop when trying to edit a cart rule. This occurs when you have many rules and are using the compatibility section. A poorly constructed query in Prestashop's code causes the page to take an extraordinarily long time to run or just time out. On one client's site it was browsing over one billion rows!
This is for versions 1.5+. The following solution has been tested in version 1.5.6.0.
The queries are located in /classes/CartRule.php in the function getCartRuleCombinations().
To fix create a file in the override/classes/ folder called CartRule.php
Copy the following code to this file and upload.
<?php
class CartRule extends CartRuleCore {
protected function getCartRuleCombinations()
{
$array = array();
$array['selected'] = Db::getInstance()->executeS('
SELECT cr.*, crl.*, 1 as selected
FROM '._DB_PREFIX_.'cart_rule cr
LEFT JOIN '._DB_PREFIX_.'cart_rule_lang crl ON (cr.id_cart_rule = crl.id_cart_rule AND crl.id_lang = '.(int)Context::getContext()->language->id.')
WHERE cr.id_cart_rule != '.(int)$this->id.'
AND (
cr.cart_rule_restriction = 0
OR cr.id_cart_rule IN (
SELECT id_cart_rule_1 AS id_cart_rule FROM '._DB_PREFIX_.'cart_rule_combination WHERE '.(int)$this->id.' = id_cart_rule_2
UNION
SELECT id_cart_rule_2 AS id_cart_rule FROM '._DB_PREFIX_.'cart_rule_combination WHERE '.(int)$this->id.' = id_cart_rule_1
)
)');
$array['unselected'] = Db::getInstance()->executeS('
SELECT cr.*, crl.*, 1 as selected
FROM '._DB_PREFIX_.'cart_rule cr
LEFT JOIN '._DB_PREFIX_.'cart_rule_lang crl ON (cr.id_cart_rule = crl.id_cart_rule AND crl.id_lang = '.(int)Context::getContext()->language->id.')
WHERE cr.cart_rule_restriction = 1
AND cr.id_cart_rule != '.(int)$this->id.'
AND cr.id_cart_rule NOT IN (
SELECT id_cart_rule_1 AS id_cart_rule FROM '._DB_PREFIX_.'cart_rule_combination WHERE '.(int)$this->id.' = id_cart_rule_2
UNION
SELECT id_cart_rule_2 AS id_cart_rule FROM '._DB_PREFIX_.'cart_rule_combination WHERE '.(int)$this->id.' = id_cart_rule_1
)');
return $array;
}
}
If the override file is not taking effect then don't forget to delete the file /cache/class_index.php. This will refresh the include files list.