Magento – Updating Configurable Products Constraint Exception

configurable-product

Using the below PHP to save a Configurable Product, upon saving an existing product we are receiving the below exception.

Message: SQLSTATE[23000]: Integrity constraint violation: 1062
Duplicate entry '32-135' for key
'UNQ_CATALOG_PRODUCT_SUPER_ATTRIBUTE_PRODUCT_ID_ATTRIBUTE_ID'

  include_once '../../app/Mage.php';

  Mage::app();

  $sku = $_POST["sku"];
  $product = Mage::getModel('catalog/product')->loadByAttribute('sku', $sku);
  $isNew = false;

  if($product == null){
    $product = Mage::getModel('catalog/product');
    $product->setSku($sku);
    $isNew = true;
  } else {
    $product->load($product->getProductId());
  }

  $product->setName($_POST["name"]);
  $product->setDescription($_POST["description"]);
  $product->setShortDescription($_POST["short_description"]);
  $product->setPrice($_POST["price"]);
  $product->setTypeId('configurable');
  $product->setAttributeSetId($_POST["attributeSet_id"]);
  $product->setCategoryIds($_POST["categories"]);
  $product->setWeight($_POST["weight"]);
  $product->setTaxClassId($_POST["tax_class"]);
  $product->setVisibility($_POST["visibility"]);
  $product->setStatus($_POST["status"]);
  $product->setMetaTitle($_POST["meta_title"]);
  $product->setMetaDescription($_POST["meta_description"]);
  $product->setWebsiteIds(array(Mage::app()->getStore(true)->getWebsite()->getId()));
  $product->setStockData(array(
                          'manage_stock'=>$_POST["manage_stock"],
                          'is_in_stock'=>$_POST["is_in_stock"],
                          'qty'=>$_POST["qty"],
                          'use_config_manage_stock'=>$_POST["use_config_manage_stock"]
                          ));
  $product->setCanSaveConfigurableAttributes(true);
  $product->setCanSaveCustomOptions(true);

  //Process simple Products and there associated Attributes
  $simpleProducts = explode(",", $_POST["simpleProducts"]);
  $productData = array();
  $attribute_ids = array();

  foreach ($simpleProducts as $simpleProduct) {
    $productAttr = Mage::getModel('catalog/product')->load($simpleProduct);
    $attributes = Mage::getModel('catalog/product_attribute_api')->items($_POST["attributeSet_id"]);

    $productData[$simpleProduct] = array();

    foreach ($attributes as $attr) {
        $model = Mage::getModel('catalog/entity_attribute')->load($attr["attribute_id"]);
        $type = $model->getFrontendInput();

        if ($model->getIsUserDefined() && $type == "select") {
            $id = $productAttr->getResource()->getAttribute($attr["code"])->getId();
            $val = $productAttr->getResource()->getAttribute($attr["code"])->getFrontend()->getValue($productAttr);
            $lab = $productAttr->getResource()->getAttribute($attr["code"])->getFrontend()->getLabel($productAttr);

            $idx;
            $options = $productAttr->getResource()->getAttribute($attr["code"])->getSource()->getAllOptions(false);

            foreach ($options as $option) {
                if (in_array($val, $option)) {
                    $idx = $option['value'];
                }
            }

        if (!in_array($id, $attribute_ids)) {
          array_push($attribute_ids, $id);
        }

            array_push($productData[$simpleProduct], array(
                                'attribute_id' => $id,
                                'label' => $lab,
                                'value_index' => $idx,
                                'is_percent' => 0,
                                'pricing_value' => 0
                            ));
      }
    }
  }

  if($isNew) {
    $product->getTypeInstance()->setUsedProductAttributeIds($attribute_ids);
    $attributes_array = $product->getTypeInstance()->getConfigurableAttributesAsArray();
    $product->setConfigurableAttributesData($attributes_array);
    $product->setConfigurableProductsData($productData);

    $product->save();
  } else{
    $groupPrices  = $product->group_price;

    //delete existing data to remove potential integrity constraint exceptions
    $product->setData('group_price',array());
    $product->getTypeInstance()->setUsedProductAttributeIds(array());
    $attributes_array = $product->getTypeInstance()->getConfigurableAttributesAsArray();
    $product->setConfigurableAttributesData(array());
    $product->setConfigurableProductsData(array());

    $product->save();
        $product->load();

        $product->setData('group_price',$groupPrices);
    $product->getTypeInstance()->setUsedProductAttributeIds($attribute_ids);
    $attributes_array = $product->getTypeInstance()->getConfigurableAttributesAsArray();
    $product->setConfigurableAttributesData($attributes_array);
    $product->setConfigurableProductsData($productData);

    $product->save();
  }

  echo $product->getId();

We have tried this approach by clearing the arrays and saving before attempting to save the new settings as this worked for the product group prices however even this approach is not working either.

Any help will be appreciated.

Best Answer

I run into the same problem and landed here. After some research, I have found that even if you set ConfigurableAttributesData with an empty array would not help as the value is only inserted and not deleted. In order to achieve what you need, you will need to remove the entries for all the super attribute that match the product id before calling setConfigurableAttributesData(). Here is what worked for me

$resource = Mage::getSingleton('core/resource');
$write = $resource->getConnection('core_write');
$table = $resource->getTableName('catalog/product_super_attribute');
$write->delete($table,"product_id = " . $product->getId());
Related Topic