Loading Thousands of Codes to Map to a Shopping Cart Rule in Magento

Question

I've been looking at the salesrule_coupon table, and I've discovered that I can map many coupon codes to a single rule, if the rule itself is of type 'Auto.' This is highly convenient as my client needs us to sync the codes periodically with a feed of data.

So in loading in these thousands of codes (using a custom module & direct SQL calls) they load just fine, and I can test and verify that many of them work.

However in working my way down the list of these codes, they stop working. The first 30 or so will work just fine, but thereafter, Magento says that the codes are invalid.

I'm still debugging this, and I'll post updates if I discover anything... but I've tried and experienced this with two separate price rules now. One rule crapped out at the 31st code, the second at the 39th.

What's really strange is that, if I change these codes to point to a different rule (one with less than 30 codes) they're recognized and accepted. Nothing else changed, that I can determine.

Any ideas on how to proceed here? Has anyone attempted this before? This is an interesting one.

Answer 1

I fixed the same issue when I was creating something similar for one of my customers. The source of the problem for retrieving of valid coupon Magento Core Sales Rule module uses FIND_IN_SET() with GROUP_CONCAT() MySQL functions instead adding additional condition for joined table. So FIND_IN_SET just truncates number of coupon codes that are used in group concatenation to 31 item (32 bits mask). Also I noticed that they are using HAVING instead of where, so it affects performance a bit.

So what you need to do are the following:

1. Create rewrite for this resource model: Mage_SalesRule_Model_Mysql4_Rule_Collection (salesrule/rule_collection)

2. Then in your resource model that rewrites core one, you need to redefine this method setValidationFilter($websiteId, $customerGroupId, $couponCode='', $now=null) that applies limitations for sales rules on the frontend. Here the method body that I used:

   /**
    * Fix for validation with auto-coupons
    * @todo remove this fix, after the bug in core will be fixed
    *
    * (non-PHPdoc)
    * @see Mage_SalesRule_Model_Mysql4_Rule_Collection::setValidationFilter()
    */
   public function setValidationFilter($websiteId, $customerGroupId, $couponCode='', $now=null)
   {
       if (is_null($now)) {
           $now = Mage::getModel('core/date')->date('Y-m-d');
       }
   
       $this->getSelect()->where('is_active=1');
       $this->getSelect()->where('find_in_set(?, website_ids)', (int)$websiteId);
       $this->getSelect()->where('find_in_set(?, customer_group_ids)', (int)$customerGroupId);
   
       if ($couponCode) {
           $couponCondition = $this->getConnection()->quoteInto(
               'extra_coupon.code = ?',
               $couponCode
           );
   
           $this->getSelect()->joinLeft(
               array('extra_coupon' => $this->getTable('salesrule/coupon')),
               'extra_coupon.rule_id = main_table.rule_id AND extra_coupon.is_primary IS NULL AND ' . $couponCondition,
               array()
           );
           $this->getSelect()->where('('
               . $this->getSelect()->getAdapter()->quoteInto(' main_table.coupon_type <> ?', Mage_SalesRule_Model_Rule::COUPON_TYPE_SPECIFIC)
               . $this->getSelect()->getAdapter()->quoteInto(' OR primary_coupon.code = ?', $couponCode) . ')'
           );
           $this->getSelect()->where('('
               . $this->getSelect()->getAdapter()->quoteInto(' main_table.coupon_type <> ?', Mage_SalesRule_Model_Rule::COUPON_TYPE_AUTO)
               . $this->getSelect()->getAdapter()->quoteInto(' OR extra_coupon.code IS NOT NULL') . ')'
           );
       } else {
           $this->getSelect()->where('main_table.coupon_type = ?', Mage_SalesRule_Model_Rule::COUPON_TYPE_NO_COUPON);
       }
       $this->getSelect()->where('from_date is null or from_date<=?', $now);
       $this->getSelect()->where('to_date is null or to_date>=?', $now);
       $this->getSelect()->order('sort_order');
   
       return $this;
   }
   

3. Test fix & Enjoy Magento Development :)