I am trying to apply catalog rule for 27% from 2 customer group but it's not applied after indexing and cleaning cache.

So i debug the issue and found that catalogrule_product_price and catalogrule_product are empty. I am using Magento ver.

Following query has been generate while apply ruel

INSERT INTO `catalogrule_product_price` SELECT NULL AS `rule_product_price_id`, `dates`.`rule_date`, `t`.`customer_group_id`, `t`.`product_id`, MIN(rule_price) AS `rule_price`, 1 AS `website_id`, `t`.`latest_start_date`, `t`.`earliest_end_date` FROM (SELECT `cppt`.`customer_group_id`, `cppt`.`product_id`, CASE WHEN IFNULL((@group_id), 'N/A') != cppt.grouped_id THEN @price := CASE `cppt`.`action_operator` WHEN 'to_percent' THEN cppt.price * cppt.action_amount/100 WHEN 'by_percent' THEN cppt.price * (1 - cppt.action_amount/100) WHEN 'to_fixed' THEN IF((cppt.action_amount < cppt.price), cppt.action_amount, cppt.price) WHEN 'by_fixed' THEN IF((0 > cppt.price - cppt.action_amount), 0, cppt.price - cppt.action_amount) END WHEN IFNULL((@group_id), 'N/A') = cppt.grouped_id AND IFNULL((@action_stop), 0) = 0 THEN @price := CASE `cppt`.`action_operator` WHEN 'to_percent' THEN @price * cppt.action_amount/100 WHEN 'by_percent' THEN @price * (1 - cppt.action_amount/100) WHEN 'to_fixed' THEN IF((cppt.action_amount < @price), cppt.action_amount, @price) WHEN 'by_fixed' THEN IF((0 > @price - cppt.action_amount), 0, @price - cppt.action_amount) END ELSE @price := @price END AS `rule_price`, `cppt`.`from_date` AS `latest_start_date`, `cppt`.`to_date` AS `earliest_end_date`, CASE WHEN IFNULL((@group_id), 'N/A') != cppt.grouped_id THEN @action_stop := cppt.action_stop WHEN IFNULL((@group_id), 'N/A') = cppt.grouped_id THEN @action_stop := IFNULL((@action_stop), 0) + cppt.action_stop END, @group_id := cppt.grouped_id, `cppt`.`from_time`, `cppt`.`to_time` FROM `catalogrule_product_price_tmp` AS `cppt` ORDER BY `cppt`.`grouped_id` ASC, `cppt`.`sort_order` ASC, `cppt`.`rule_product_id` ASC) AS `t` INNER JOIN (SELECT DATE_ADD(FROM_UNIXTIME(1494181800), INTERVAL -1 DAY) AS rule_date UNION SELECT FROM_UNIXTIME(1494181800) AS rule_date UNION SELECT DATE_ADD(FROM_UNIXTIME(1494181800), INTERVAL 1 DAY) AS rule_date) AS `dates` ON 1=1 WHERE (UNIX_TIMESTAMP(dates.rule_date) >= from_time) AND (IF((to_time = 0), 1, UNIX_TIMESTAMP(dates.rule_date) <= to_time)) GROUP BY `customer_group_id`, `product_id`, `dates`.`rule_date`
  • I have the same issue. Have you solved your problem? – Malou72 Nov 23 '18 at 13:32
  • This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. You can also add a bounty to draw more attention to this question once you have enough reputation. - From Review – Rama Chandran M Nov 23 '18 at 13:53

This works for magento 2, but I think it works also in 1.9


Insert in catalogrule

INSERT INTO `catalogrule` (`rule_id`, `name`, `description`, `from_date`, `to_date`, `is_active`, `conditions_serialized`, `actions_serialized`, `stop_rules_processing`, `sort_order`, `simple_action`, `discount_amount`) VALUES (2, '27% from 2 customer group', '', NULL, NULL, 1, '{"type":"Magento\\\\CatalogRule\\\\Model\\\\Rule\\\\Condition\\\\Combine","attribute":null,"operator":null,"value":"1","is_value_processed":null,"aggregator":"all"}', '{"type":"Magento\\\\CatalogRule\\\\Model\\\\Rule\\\\Action\\\\Collection","attribute":null,"operator":"=","value":null}', 0, 0, 'by_percent', 27.0000);

in catalogrule_customer_group

INSERT INTO `catalogrule_customer_group` (`rule_id`, `customer_group_id`) VALUES (2, 1);
INSERT INTO `catalogrule_customer_group` (`rule_id`, `customer_group_id`) VALUES (2, 2);

in catalogrule_group_website

INSERT INTO `catalogrule_group_website` (`rule_id`, `customer_group_id`, `website_id`) VALUES (2, 1, 1);
INSERT INTO `catalogrule_group_website` (`rule_id`, `customer_group_id`, `website_id`) VALUES (2, 2, 1);

in catalogrule_website

INSERT INTO `catalogrule_website` (`rule_id`, `website_id`) VALUES (2, 1);

Dont forget to clean cache and reindex. if it does not work try to enter to the back and in Catalog Rules try to apply rules

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.