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.

Ivan Chepurnyi · Accepted Answer · 2011-06-08T20:20:40.293

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:

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

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;
}

Test fix & Enjoy Magento Development :)

Interesting! Yeah, one tiny bug, you don't need that `quoteInto` if you're not doing any replacement (on the `OR extra_coupon` bit). I put that in single quotes and it worked great. Thanks for the insights! — bahoo, Jun 09 '11 at 00:11

score 4 · Answer 2 · answered Jun 08 '11 at 22:09

4

Another solution is to increase the mysql limit you are running into with

SET GLOBAL group_concat_max_len=9999999;

as Ivan explained the FIND_IN_SET does not return all your coupon codes. You need to increase group_concat_max_len to be able to hold the length of all your coupon codes delimited by a comma (COUPON1, COUPON2, COUPON3).

Since you have likely used different codes with different lengths, this would explain why 1 rule worked for 30 while the other worked for 38.

answered Jun 08 '11 at 22:09

Fooman

1,340
9
11

Good to know, though I worry there might be performance implications (& worse, a max length is something of a moving target on my project). At any rate, I appreciate the help! – bahoo Jun 09 '11 at 00:14

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

2 Answers2