Update all Tier prices for a product

Question

Without directly querying the Magento database. How can i remove all the tier prices for a certain product based on quantity and customer group?

Then add new tier prices for this product.

Example:
Remove all tier prices for a product with the SKU: FD001
where the customer group id is: 4

PHP 5.3 Magento 1.4.2

Infielder

Answer 1

I ended up solving this by using direct database queries.

As always I'm looking for a better answer.

My Hacky solution:

$product = Mage::getModel('catalog/product')->load(9999);
$dbc = Mage::getSingleton('core/resource')->getConnection('core_write');
$dbc->query('DELETE FROM `catalog_product_entity_tier_price` WHERE `entity_id` = ' . intval($product->getId()) . ' AND `customer_group_id` IN(3,4,6,7) AND qty = 1');
$dbc->query(
    'INSERT INTO `catalog_product_entity_tier_price` (`entity_id`,`all_groups`,`customer_group_id`,`qty`,`value`,`website_id`)
    VALUES ('. intval($product->getId()) . ',0,' . intval($id) . ',1,' . floatval($price) . ',0)'
    );

Infielder

Answer 2

You first have to unset the tier prices and save the product, then add the tier price(s) and save again.

Mage::getModel("catalog/product")->load(123)
    ->unsTierPrice()
    ->save()
    ->setTierPrice(array(
        array(
            'website_id'    => 0,
            'all_groups'    => 0,
            'cust_group'    => 4,
            'price'         => 99.99,
            'price_qty'     => 1
        ),
        array() // another tier, etc
    ))
    ->save();

Shields

I can confirm this works. However between your first save and and setting the new tier price you need another load. At least for 1.9.1. Mage::getModel("catalog/product")->load(123) ->unsTierPrice() ->save() ->load() ->setTierPrice(array( array( 'website_id' => 0, 'all_groups' => 0, 'cust_group' => 4, 'price' => 99.99, 'price_qty' => 1 ), array() // another tier, etc )) ->save(); – Helium

Answer 3

There's an API available for the product tier price.

Alternatively, you can use some PHP code that uses the Magento code to query for a list of products that match those criteria and then adjust each one. Alan Storm has an article about how Model Collections work (they're the type of object that you would use for this).

Basically it would be something like this to delete the products, I'm not sure how you would set the tier prices, but you can a look at the generated phpdoc documentation. I'm selecting every product that matches customer_group 4 and then deletes each product. You'll have to figure out how to filter things out based on the tier price...

<?php
require 'app/Mage.php';
$app = Mage::app('default'); // Mage_Core_Model_App
$store = $app->getStore(); // Mage_Core_Model_Store

$products = Mage::getModel('catalog/product')->getCollection();
// filter out anything that doesnt match the customer group 4
$products->addFieldToFilter('customer_group', '4');

// loop through each product and delete it
for ($products->load() as $product) {
  $product->delete();
}

Biphenyl

I don't need a list of tiered prices. I want to delete them based on criteria. I was looking for something like a model that i can run delete on to remove them. – Infielder

This script deletes products, which is not what the OP wanted to do at all. – Bywaters

Answer 4

@omouse's answer pointed us to Magento's API for tier prices. [Updated Link for Magento 1.X's Tier Price API] Note: I'm working with Magento 1.9.2.2.

After looking around for the most efficient way to update a product's tier prices (without using direct SQL), I found the API is the fastest way. It's still slow, but it's better than the other methods I found which recommended doing something like:

// Works, but is slow
$product = Mage::getModel('catalog/product')->load($productId);
$product->unsTierPrice();
$product->save();
$product->load();
$product->setTierPrice($tiers);
$product->save();

Better, I made my array of tier arrays into an array of tier objects, and used the API functions to update the product:

// Build the array of tier level objects
foreach ($tierLevels as $tierLevel) {
    $tiers[] = (object) array(
        'website' => 'all',
        'customer_group_id' => 'all',
        'qty' => $tierLevel['min_qty'],
        'price' => $tierLevel['unit_price']
    );
}

// Use the API to do the update
try {
    $tierPriceApi = Mage::getSingleton('catalog/product_attribute_tierprice_api_v2');
    $tierPriceApi->update($productId, $tiers);
}
catch (Exception $e) {
    // Handle the exception
}

Gyrostabilizer

Answer 5

I searched a while to find a solution, which is not pure SQL and does not need a product save, because we want to update tier prices for a catalog of 50k products, so saving all of them is surely not a solution. I think I finally found a good way.

Mage::getResourceSingleton('catalog/product_attribute_backend_tierprice')
->deletePriceData($product->getId()); 
$newTierPrice['entity_id']         = $product->getId()
$newTierPrice['all_groups']        = 1;
$newTierPrice['customer_group_id'] = 0;
$newTierPrice['qty']               = 42;
$newTierPrice['value']             = 100;
$newTierPrice['website_id']        = 0;
Mage::getResourceSingleton('catalog/product_attribute_backend_tierprice')
            ->savePriceData(new Varien_Object($newTierPrice));

Tunicate