[BOOM-2595] Conception problem form cart rule restrictions #33442
Replies: 24 comments 2 replies
-
Hi David-Julian BUCH, This part will be revamped but we don't know yet when. We will improve the code and the UX so we take your ticket into consideration Thanks for your feedback ! |
Beta Was this translation helpful? Give feedback.
-
any update on this? |
Beta Was this translation helpful? Give feedback.
-
Yes, nearly 70Gb ! |
Beta Was this translation helpful? Give feedback.
-
@roadster31 looks like I am stealing your gold medal here 🥇 :-) This website have 119k cart rules, most of them are disabled and 1.7 billion entries in ps_cart_rule_combination While checking AdminCartRuleController.php it looks like ps_cart_rule_combination is getting filled also with disabled id_cart_rules so this could be a first optimization but yes, this really need a "core" redesign. |
Beta Was this translation helpful? Give feedback.
-
Hi, I just got a quick look at this. First, it seems the code responsible for this has been there for many years (at least 2011) 5547835 Reworking such an old piece of the software is very hard, especially due to lack of documentation on the inner working of this stuff. It does not mean it will not be done, it means it will be hard and slow 😅 Second, would it be possible to elaborate on the usecase ? the code is old and not the easiest to read, it seems you have explored what's going on. Why so many lines are put into ps_cart_rule_combination ? why current implementation is putting them there ? 😊 |
Beta Was this translation helpful? Give feedback.
-
@matks , thank you for your time ! Issue is happening when there are a lot of cart rules and a cart rule is being set as restricted (use the "Compatibility with other cart rules" flag) among other existing cart rules (that are being dinamically set as restricted with cart_rule_restriction = 1 too). Basically ps_cart_rule_combination store all the incompatible couple of rules expanding exponentially as soon as you have restricted rules. There are a lot of Addons modules that generates a lot of cart rules "on the fly" so it's not too uncommon to encounter this problem (I have seen many sites with huge ps_cart_rule_combination table ...) It's easy to reproduce yourself on a clean install.
|
Beta Was this translation helpful? Give feedback.
-
Hi! @matks @gennaris |
Beta Was this translation helpful? Give feedback.
-
I have the same issue.... Thanks, |
Beta Was this translation helpful? Give feedback.
-
@TheoAlloin maybe we could share some thoughts on priv about that issue? I suspect that some modules could be involved so we could compare some things :) |
Beta Was this translation helpful? Give feedback.
-
@sijafu yes why not :) But i think it's a real problem of conception, it doesn't necessarily come from an external module. |
Beta Was this translation helpful? Give feedback.
-
I think I've well explained the reason behind the problem in July 31st post , how to reproduce it and why it happens. Unfortunately, this function has been designed into the core without taking into account the fact that you can have numerous restricted rules. No modules are involved, unless they are creating restricted rules as-per-my previous explanation. It seems that a total redesign of this method and also of the database structure would be required to fix this. In my case, the solution was to stop using restricted rules in the involved e-commerce |
Beta Was this translation helpful? Give feedback.
-
Hi, |
Beta Was this translation helpful? Give feedback.
-
Hi, |
Beta Was this translation helpful? Give feedback.
-
Hi, we had this problem, this table was about 180GB and we needed to take action because it was taking our server. My advice now is:
We have this problem since 2018... this problem is not a problem if you don't have a lot of discounts. |
Beta Was this translation helpful? Give feedback.
-
Hi @crishnakh, Thank you for sharing, however, I wonder if by deleting the old cart_rules, we will lose some of the history in the orders not? For example, some customers look at old orders and see a discount code without knowing where it comes from. So we have to look at the cart_rules to see its description, .... In this case, the cleaning would not help the customer in the long run. Do you have any news about PretaShop? |
Beta Was this translation helpful? Give feedback.
-
Hi @Lionel-dev we are aware of this problem. In our case, we have the code of the cart_rule or the name to know about the discount. We had about 4-5 years of cart rules, and about the 80% where used or expired. We don't have any news from PrestaShop... I think the problem is still there when you have a lot of discounts. |
Beta Was this translation helpful? Give feedback.
-
Any SQL queries to do a little cleaning? Something like:
|
Beta Was this translation helpful? Give feedback.
-
Hello everyone. Following our new approach to feature requests, I converted this Issue to the GitHub Discussion so that the community can discuss the best possible ways to handle this problem and maybe, I hope, organize together to work towards fixing it in the core. If you are motivated to do so, we will be happy to help. |
Beta Was this translation helpful? Give feedback.
-
But is this crazy bug also present in PrestaShop 8.x? |
Beta Was this translation helpful? Give feedback.
-
I think is not a real issue or bug. Just simply this system is done for ecommerces who brings from 5 to 10 different discounts, so you can choose wich discounts or cart rules are compatibles with the rest of rules. The problem comes when we uses an external rules as mine. I used a gift cart module to bring the customers some free items in the cart page. Well, the module creates a discount (cart rule) for each gift the order has inside. Even if the customer doubt about the gift to choose, a new cart rule is created on every gift change. After all, if you have choosen to combinate all cart rules, the table rows grows exponencially. I'm researching about this to avoid it, but I'm affraid is not an easy thing... |
Beta Was this translation helpful? Give feedback.
-
When the voucher is used for an order, all cart_combination entries should removed isnit? Thats the bug I think. Could we delete safely rows: SELECT crc.*
FROM psds17_cart_rule_combination crc
LEFT JOIN psds17_cart_rule cr1 ON cr1.id_cart_rule = crc.id_cart_rule_1
LEFT JOIN psds17_cart_rule cr2 ON cr2.id_cart_rule = crc.id_cart_rule_2
WHERE cr1.id_cart_rule IS NULL OR cr2.id_cart_rule IS NULL OR cr1.date_to < NOW() OR cr2.date_to < NOW(); ? |
Beta Was this translation helpful? Give feedback.
-
Thanks you for details!
What about voucher for single customer and single usage (such as refund) ?
We use it a lot and this is killing our MySQL :-/
Le mar. 26 mars 2024, 07:20, Simone Gennari ***@***.***> a
écrit :
… There isn't a direct connection between the tables *ps_cart_rule_** and
the orders.
When specific cart rules are used in orders, this information is stored in
the *ps_order_cart_rule* table.
The purpose of the *ps_cart_rule_combination* table is to maintain the
relationship between the restrictions imposed by individual cart rules.
If you're relying on single-use vouchers, you can set up a crontask to
delete expired rules and you'll be fine
(*delete()* method of the CartRule class already handles the
*ps_cart_combination* cleanup)
However, this isn't applicable for rules that can be used multiple times..
—
Reply to this email directly, view it on GitHub
<#33442 (reply in thread)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/AAJJZ2JS43LLAQWZQN3MRVDY2FDVJAVCNFSM6AAAAAA64E3ICKVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4DSMJTHE4TA>
.
You are receiving this because you commented.Message ID:
***@***.***>
|
Beta Was this translation helpful? Give feedback.
-
This problem is still very common. And a simple fix would to add a notion of "Cart rule category" and to add the restriction at the category level instead of the Cart rule level. The code is not so hard to understand. |
Beta Was this translation helpful? Give feedback.
-
We had a 20go ps_cart_rule_combination slowing our server Technically, in controllers\admin\AdminCartRulesController.php , function afterAdd (I did this for version 1.7.8.7) : $limit_to_customer = '';
if (isset($currentObject->id_customer) && $currentObject->id_customer != '0'){
// Cart restricted on a specific customer
$limit_to_customer = ' AND (id_customer = 0 OR id_customer = '.(int)$currentObject->id_customer.')';
} Then modify this part, changes are in lines that contains "$limit_to_customer" // If the new rule has no cart rule restriction, then it must be added to the white list of the other cart rules that have restrictions
if (!Tools::getValue('cart_rule_restriction')) {
Db::getInstance()->execute('
INSERT INTO `' . _DB_PREFIX_ . 'cart_rule_combination` (`id_cart_rule_1`, `id_cart_rule_2`) (
SELECT id_cart_rule, ' . (int) $currentObject->id . ' FROM `' . _DB_PREFIX_ . 'cart_rule` WHERE cart_rule_restriction = 1
AND quantity != 0 AND date_to > NOW() AND active = 1 '.$limit_to_customer.'
)');
} else {
// And if the new cart rule has restrictions, previously unrestricted cart rules may now be restricted (a mug of coffee is strongly advised to understand this sentence)
$ruleCombinations = Db::getInstance()->executeS('
SELECT cr.id_cart_rule
FROM ' . _DB_PREFIX_ . 'cart_rule cr
WHERE cr.id_cart_rule != ' . (int) $currentObject->id . '
AND cr.cart_rule_restriction = 0
AND NOT EXISTS (
SELECT 1
FROM ' . _DB_PREFIX_ . 'cart_rule_combination
WHERE cr.id_cart_rule = ' . _DB_PREFIX_ . 'cart_rule_combination.id_cart_rule_2 AND ' . (int) $currentObject->id . ' = id_cart_rule_1
AND quantity != 0 AND date_to > NOW() AND active = 1 '.$limit_to_customer.'
)
AND NOT EXISTS (
SELECT 1
FROM ' . _DB_PREFIX_ . 'cart_rule_combination
WHERE cr.id_cart_rule = ' . _DB_PREFIX_ . 'cart_rule_combination.id_cart_rule_1 AND ' . (int) $currentObject->id . ' = id_cart_rule_2
AND quantity != 0 AND date_to > NOW() AND active = 1 '.$limit_to_customer.'
)
');
foreach ($ruleCombinations as $incompatibleRule) {
Db::getInstance()->execute('UPDATE `' . _DB_PREFIX_ . 'cart_rule` SET cart_rule_restriction = 1 WHERE id_cart_rule = ' . (int) $incompatibleRule['id_cart_rule'] . ' LIMIT 1');
Db::getInstance()->execute('
INSERT IGNORE INTO `' . _DB_PREFIX_ . 'cart_rule_combination` (`id_cart_rule_1`, `id_cart_rule_2`) (
SELECT id_cart_rule, ' . (int) $incompatibleRule['id_cart_rule'] . ' FROM `' . _DB_PREFIX_ . 'cart_rule`
WHERE active = 1 AND quantity != 0 AND date_to > NOW() '.$limit_to_customer.'
AND id_cart_rule != ' . (int) $currentObject->id . '
AND id_cart_rule != ' . (int) $incompatibleRule['id_cart_rule'] . '
)');
}
} We also deleted cart_rule_combination for cart_rule expired or consumed with this (could be long) : <?php
// Chargement de la configuration de Prestashop
include(dirname(__FILE__) . '/../config/config.inc.php');
include(dirname(__FILE__) . '/../init.php');
// Connexion à la base de données via le connecteur Prestashop
$db = Db::getInstance();
$sql = 'SELECT cr.id_cart_rule FROM '._DB_PREFIX_.'cart_rule cr WHERE cr.quantity = 0 OR cr.date_to < NOW() OR cr.active = 0';
$results = $db->executeS($sql);
print_r(count($results));
echo '<br>';
echo '<br>';
if ($results) {
$i=1;
foreach ($results as $result) {
$sql = 'DELETE crc
FROM '._DB_PREFIX_.'cart_rule_combination crc
WHERE crc.id_cart_rule_1 = '.$result['id_cart_rule'].' OR crc.id_cart_rule_2 = '.$result['id_cart_rule'];
// Exécution de la requête
$result_delete = $db->execute($sql);
if ($result_delete) {
echo ' ' . $result['id_cart_rule'] . ' => Unused cart rule combinations have been successfully deleted. ( loop ' . $i . ' )<br>';
} else {
echo ' ' . $result['id_cart_rule']. ' => Failed to delete unused cart rule combinations. ( loop ' . $i . ' )<br>';
}
$i++;
}
echo '<br>';
} else {
echo 'No results found.';
} You need to optimize the sql table after that. That shrinked our table from 20go to 9mo... |
Beta Was this translation helpful? Give feedback.
-
Hello folks,
There is a huge conception problem for cart rule restrictions.
In case a website has just 5 000 (for example) unrestricted cart rules, and the user adds a restricted cart rules, the lines starting at line 338 of file controllers/admin/AdminCartRulesController.php will start inserting 25 millions of rows in the table "ps_cart_rule_combination", just making the whole site hang when using a cart rule.
We should consider to rethink this part. Maybe adding cart rule types and only enabling cart rule types restrictions which would lead to much less rows (because most of the time those 5000 cart rules could be split in 20 or 30 cart rule types).
Regards,
David
Beta Was this translation helpful? Give feedback.
All reactions