Setting (and updating) product group prices

Question

It might be quite a simple question, but I've found no valid related answer yet.

I'm trying to import sales prices from CRM with a custom module.

I've tried:

$item->setData('group_price', array(array ('website_id'=>0, 'cust_group'=>$custGroup->getId(), 'price'=>$price->precio)));

But when the price for the customer group does exist I get the following error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '14729-0-784-0' for key 'CC12C83765B562314470A24F2BDD0F36'

I've tried several things, including obtaining existing data with:

$group_prices = $item->getData('group_price');

in order to merge results with the new one, deleting all group prices before I set them again. But I don't seem to get the group prices array I was expecting...

Can anyone help to get this solved?

score 5 · Answer 1 · answered Feb 13 '14 at 16:49

Group pricing in Magento is relatively new and not a lot of model based functionality is available for this.

If you have the Store ID handy you can remove a single group price or include the new price as follows:

$product = Mage::getModel('catalog/product')->setStoreId(2)->load(1234);
$product->setData('group_price',array ());
$product->save();

Unfortunately, the situations I've faced have involved bulk group price updating and running the above multiple times isn't ideal. In which case I have had to use Direct SQL to clear the data for a particular product so you don't get the duplicate entry error followed by adding your new group prices again.

/**
 * Clear out the group price table for a product
 *
 * @param Mage_Catalog_Model_Product $product
 */
private function _emptyGroupPrices($product) {
    /* @var $sql Mage_Core_Model_Resource */
    $sql = Mage::getSingleton('core/resource')->getConnection('core_write');
    $sql->query('delete from `catalog_product_entity_group_price` where `entity_id` = '.$product->getId());
}

Thanks, Raj, but I think I found another solution and I just don't like to make direct SQL changes in Magento's database. You might always be missing something that should be executed right before or after the database modification. Anyway, if I find my answer to be incorrect, I'll use your solution instead. — Jared, Feb 17 '14 at 10:16
I'd vote up your answer, but I just don't have enough points to reward your help. — Jared, Feb 17 '14 at 10:17
Yeah, it is a rather crude way of doing it - unfortunately, I can't find a better way. Don't worry about not being able to vote up the answer - I know the feeling as I can't vote up answers yet either. — Raj, Feb 18 '14 at 09:53

score 3 · Accepted Answer · answered Feb 17 '14 at 10:13

Finally I think I came out with a solution.

It occured to me, as I said, that since group_prices is an array assigned to 'group_price' attribute for item, what I should do is to set the whole array everytime. So:

$group_prices = $item->getData('group_price');
if (is_null($group_prices)) {
    $attribute = $item->getResource()->getAttribute('group_price');
    if ($attribute) {
        $attribute->getBackend()->afterLoad($item);
        $group_prices = $item->getData('group_price');
    }
}


if(!is_array($group_prices)){
    $group_prices = array();
}

$new_price = array(array ('website_id'=>0, 
              'cust_group'=>$custGroup->getId(), 
              'price'=>$price->precio));

$group_prices = array_merge($group_prices, $new_price);
$item->setData('group_price', $group_prices);

Looks like it worked just fine, anyway, I wouldn't mind if someone completed or corrected this answer.

This doesn't allow you to update a product that has an existing group price at least for me this didn't work — Joel Davey, Jun 15 '15 at 11:37

score 1 · Answer 3 · answered Jun 15 '15 at 14:47

There doesn't seem to be a reliable way to do this, I've tried the above and tried other methods e.g updateAttributes. Here's my version for re-indexing and clearing Lesti's FPC along with updating the grouped product.

 private function _emptyGroupPrices($product) {
    /* @var $sql Mage_Core_Model_Resource */
    $sql = Mage::getSingleton('core/resource')->getConnection('core_write');
    $sql->query('delete from `catalog_product_entity_group_price` where `entity_id` = '.$product->getId());
}

 private function _updateGroupPrice($product,$groupPrice,$groupId) {
    $sql = Mage::getSingleton('core/resource')->getConnection('core_write');
    $sql->query('INSERT INTO `catalog_product_entity_group_price` (entity_id,all_groups,customer_group_id,value,website_id) VALUES ("'.$product->getId().'","0","'.$groupId.'","'.$groupPrice.'","0")');
}

$newPrice = "10.00";

$this->_emptyGroupPrices($product);
$this->_updateGroupPrice($product,$newPrice);

Mage::getResourceModel('catalog/product_indexer_price')->reindexProductIds($product->getId());
Mage::getSingleton('fpc/fpc')->clean(sha1('product_' . $product->getId()));

$ids = $this->_getAssociatedProductIds($product->getId());
foreach($ids as $id) {
    Mage::getResourceModel('catalog/product_indexer_price')->reindexProductIds($id);
    Mage::getSingleton('fpc/fpc')->clean(sha1('product_' . $id));
}

Setting (and updating) product group prices

3 Answers3