Magento: Display tier prices at category listing page

Question

I'm trying to display all tier prices on category listing page (catalog/product/list.phtml) but only getting the basic price for a single product.

print_r($_product->getTierPrice());

returns:

Array
(
    [0] => Array
        (
            [price] => 0.5000
            [website_price] => 0.5000
            [price_qty] => 1
            [cust_group] => 32000
        )

)

for each product. On the product info page it works w/o problems. Please advise.

ver. 1.5.0.1

UPDATE:

Here is the solution for the problem inspired with the answer below:

$resource = Mage::getSingleton('core/resource');
$query = 'SELECT * FROM ' . $resource->getTableName('catalog/product') . '_tier_price';
$_tier_prices = $resource->getConnection('core_read')->fetchAll($query);
var_dump($_tier_prices);

Answer 1

All objects in Magento can potentially be created differently on different pages, and have different data in them. It might seem unintuitive at first. It happens that the db query that loads the data into the $_product object on the Item page has pretty much "all" the data in it. But for optimization purposes the $_product used on the category page only has some of the data - and if I remember correctly, it even pulls from different db tables. For example, the the query on the category page joins against the catalogindex* tables for some of the data that would normally be retrieved from the regular eav table.

I don't have any specifics to give to you but you can look at querying directly against the catalog_product_entity_tier_price table, which has all the tier pricing. At least that is the table name in my version of magento, which isn't 1.5. Side effect would be that the category page will take longer to load due to the extra query/queries.

Answer 2

This is another way to get the tier prices

Mage::getResourceModel('catalog/product_attribute_backend_tierprice')
            ->loadPriceData($product_id,Mage::app()->getWebsite()->getId());

The response is array with prices

[0] => Array
    (
        [price_id] => 7
        [website_id] => 0
        [all_groups] => 1
        [cust_group] => 0
        [price] => 32.0000
        [price_qty] => 6.0000
    )

[1] => Array
    (
        [price_id] => 8
        [website_id] => 0
        [all_groups] => 1
        [cust_group] => 0
        [price] => 31.0000
        [price_qty] => 12.0000
    )

Answer 3

If you are looking for a Magento way:

 $attribute = $_product->getResource()->getAttribute('tier_price');

    if ($attribute) {
       $attribute->getBackend()->afterLoad($_product);
       $tierPrices = $_product->getTierPrice();
    }

From /app/code/core/Mage/Catalog/Model/Product/Type/Price.php getTierPrice()

Answer 4

It's been a while since this was asked/answered, but I just needed to do this and found a better way, so as to not use direct database access.

As mentioned before, the $_product object by default doesn't contain the $_tierPrices on the product list page ... but if you set the tier_price value to null, it retrieves the actual values from the database and populates the object. So, wherever you need the tier prices, add:

$_product->setData('tier_price',null);
$_tierPrices = $this->getTierPrices($_product); 

This should ensure the tier prices are populated in the object, so you can use it on any page.

Just keep in mind this does still incur the same performance hit as the direct db access.

Answer 5

I've tried the answers above and some others online and none of them worked so I put together a few hacks into 1 and this is how you can get it to work anywhere. I even have a custom Home Page Carousel that is pulling it like this.

1. You start off by making a connection somewhere near the top outside of the loop

$wh_resource = Mage::getSingleton('core/resource');
$wh_readConnection = $wh_resource->getConnection('core_read');

2. Get the customer id

$wh_customer = Mage::getSingleton('customer/session')->getCustomer();
$wh_id = $wh_customer->getGroupId();

3. Next we go into the loop where you have your price echo

Note: below im using a hard code of 2 because Wholesale is 2 by default. You can build your own if/else & queries

if($wh_id == 2){
//get id
$_eid = $_helper->productAttribute($_product, $_product->getId(), 'entity_id');
$wh_query = 'SELECT * FROM catalog_product_entity_group_price WHERE entity_id = '.$_eid.' LIMIT 1';
$wh_results = $wh_readConnection->fetchAll($wh_query);
//var_dump($wh_results);
/* var dump would look like this
array(1) { [0]=> 
    array(6) { 
    ["value_id"]=> string(1) "1" 
    ["entity_id"]=> string(1) "3" 
    ["all_groups"]=> string(1) "0" 
    ["customer_group_id"]=> string(1) "2" 
    ["value"]=> string(6) "9.5000" 
    ["website_id"]=> string(1) "0" 
    } 
}
*/
$wh_price = substr($wh_results[0]["value"], 0, -2); // this damn database gives us extra 00
echo '<div class="price-box"><span class="regular-price"><span class="price">$'.$wh_price.'</span></span></div>';
} else {
//not wholesale
echo $this->getPriceHtml($_product, true);
}

Well, that's how I did it. Feel free to contact me if you need any help

Answer 6

Inside app/design/frontend/your_theme/default/template/catalog/product/list.phtml add this inside the foreach loops for grid and/or list

<?php $this->setProduct(Mage::getModel('catalog/product')->setStoreId(Mage::app()->getStore()->getId())->load($_product->getId()))?>
<?php echo $this->getTierPriceHtml() ?> 

Answer 7

Go to the Manage Attributes and edit the attributes that you want to appear.

Maybe the products don't appear in the product listing because they wasn't configured to do that, so, in the edit attribute page, check if this is activated:

Used in Product Listing