How do I exclude products with discount from coupon codes?

Question

I need put a coupon code. But I don't need the products with any discount (catalog price rule). I want to make discount coupon only for products with normal price.

Is this even possible?

Answer 1

Here's how I solved this problem:

1. Create a new product attribute (e.g. 'catalog_rule_active' type Yes/No) and attach to your attribute set. Make sure you set "Use For Promo Rule Conditions" to Yes in storefront properties.

2. Create a cron job that runs shortly after the default catalogrule_apply_all cronjob (default run time is 1am) or schedule to your requirements.

• the apply all cronjob updates the catalogrule_product table in your db. This table stores all the product_ids that Catalog Rules are applied to.
• your cron job needs to collect the product ids from this table
• then your cron should retrieve a product collection of all items that have the catalog_rule_active attribute set to Yes
• then you can use the active product ids to update the attribute to Yes
• and then you can filter out the expiring product ids, and update the attribute to No

see example - customise logic as required

    public function execute()
    {
     try {
          $connection = $this->resourceConnection->getConnection();
          $tableName = $this->resourceConnection->getTableName('catalogrule_product');
          $date = strtotime($this->dateTime->gmtDate());

          $select = $connection->select()
              ->from($tableName)
              ->where('from_time = 0 or from_time < ?', $date)
              ->where('to_time = 0 or to_time > ?', $date);

          $ruleProducts = $connection->fetchAll($select);

          $productIds = [];
          foreach ($ruleProducts as $ruleProduct) {
            $productIds[] = $ruleProduct['product_id'];
          }
          $productIds = array_unique($productIds);

          $productCollection = $this->productCollection->create()
                              ->addAttributeToFilter('catalog_rule_active', 1);
          $oldProducts = [];
          foreach ($productCollection as $product) {
            $oldProducts[] = $product->getId();
          }

          $productsToRemove = array_diff($oldProducts, $productIds);

          foreach ($productIds as $productId) {
            $product = $this->productModel->load($productId);
            $product->setData('catalog_rule_active', 1);
            $product->save();
          }

          foreach ($productsToRemove as $removals) {
            $product = $this->productModel->load($removals);
            $product->setData('catalog_rule_active', 0);
            $product->save();
          }
    } catch (\Exception $e) {
        $this->_logger->error($e->getMessage());
    }
}

3. When you set up any Sales Cart Price rule, set your desired conditions (brand, category, etc) and then add a second condition that excludes any product that has this catalog_rule_active attribute set to Yes.

The added benefit of this is then you can add your new attribute to filters/columns in the admin Product screen so you can easily see any products that are currently captured by a catalog rule :)

Update: I modified the code from this original answer to capture configurable products as well. You can see the fullcode here https://github.com/bernieu2/magento2-catalogruleactive