I would like to do following on

Getting 12 random upsell-products from same category as current product. They have to be saleable. They shouldnt be in related-products already. They shouldnt be in cart.

And if there are not 12 in that category fill the rest with products from parent category.

I think this is getting complex but I can do it. Just not sure if I can do it good :-)

My Question is do I have to worry about performance with that select complexity? What do you mean?

Code examples would be great that I can compare them.

  • I think it would slow down you site for quite a bit (if you have 100+ categories and 2000+ products). You ask for code, but what have you been able to code so far? Commented Jul 11, 2014 at 13:50
  • Well, I didnt start at all because I wanted to be aware of the performance issue before :) But I know how to do it. With your comment in mind I think I just try it now and ask for optimization of code here later if it even is a problem. THX Commented Jul 11, 2014 at 14:07

3 Answers 3


I recently implemented something very similar and will share the relevant code below. But first some comments about performance, since that was the question:

General thoughts on performance for your requirements:

  • "they shouldn't be in cart" means, you have only limited means to cache the upsell block. I strongly recommend to do it anyway, but set cache key and cache lifetime only if the cart is empty.
  • Don't ORDER BY RAND() because it results in a resource intensive temp table copy. It has to load all results into a temporary table, assign a random number to each row and then sort without any index. Instead we retrieve all candidate ids (this is faster and the amount of data is managable even for large catalogs), pick some randomly and retrieve these rows directly. You can read about it in detail in my blog: http://www.schmengler-se.de/en/2015/09/show-random-products-in-magento-you-are-doing-it-wrong/

Specific performance problems with your solution

  • Don't use addAttributeToSelect('*') with EAV collections, only select what you need
  • Not entirely sure, but I doubt that new Zend_Db_Expr('FIELD(category_id, ' . implode(',', $_cat_order).')')) can efficiently use the MySQL indexes

Complete Solution

This is an observer for the catalog_product_upsell event. It uses the product collection provided by the product's category because I didn't need to fall back to the parent, but I'm sure you can adjust it.

use Mage_Catalog_Model_Product as Product;
use Mage_Catalog_Model_Product_Link as RelatedProduct;
use Mage_Catalog_Model_Resource_Product_Link_Product_Collection as RelatedProductCollection;

class IntegerNet_AutoUpsell_Model_Observer
     * @see event catalog_product_upsell
     * @param Varien_Event_Observer $observer
     * @throws Mage_Core_Exception
    public function fillUpsellCollection(Varien_Event_Observer $observer)
        $collection = $observer->getCollection();
        if ($collection instanceof RelatedProductCollection
            && $collection->getLinkModel()->getLinkTypeId() === RelatedProduct::LINK_TYPE_UPSELL
            && $collection->count() < $observer->getLimit('upsell')
        ) {
            $this->addItemsFromCategory($collection, $observer->getLimit('upsell') - $collection->count(), $observer->getProduct());
    protected function addItemsFromCategory(RelatedProductCollection $collection, $numberOfItems, Product $product)
        /** @var Mage_Catalog_Model_Resource_Product_Collection $productsToAdd */
        $productsToAdd = $this->_getProductCategory($product)->getProductCollection();
            ->addIdFilter(array_merge([$product->getId()], $collection->getAllIds()), true)

        $candidateIds = $productsToAdd->getAllIds();
        $choosenIds = [];
        $maxKey = count($candidateIds)-1;
        while (count($choosenIds) < $numberOfItems)) {
            $randomKey = mt_rand(0, $maxKey);
            $choosenIds[$randomKey] = $candidateIds[$randomKey];
        foreach ($productsToAdd as $product) {

     * @param Mage_Catalog_Model_Product $product
     * @return Mage_Catalog_Model_Category
    protected function _getProductCategory(Product $product)
        $category = $product->getCategoryCollection()
        return $category;

Some parts that I'd like to highlight:

->addIdFilter(array_merge($product->getId(), $collection->getAllIds()), true)

This excludes the product itself and the products that are already manually defined as upsell products (I leave them and only fill the blanks with random products)


This prepares the product collection to load the necessary data to display prices, the product link and any attributes configured as "used in product listing", but not more.

  • Ok, this is awesome :) Thank you very much for such detailed information! Commented Jul 20, 2015 at 8:55

Here is my working code:

//get needed variables
$_current_prod = Mage::registry('current_product');
$_current_cat =  Mage::getModel('catalog/layer')->getCurrentCategory();
$_current_cat_id = $_current_cat->getId();
$_parent_cat_id =  $_current_cat->getParentId();
//did array because later on it may get more complex order with more ids or I want to sort it different
$_cat_order = array($_current_cat_id,$_parent_cat_id);
//getting related products to filter from collection
$_related_products = $_current_prod->getRelatedProductIds();

//getting cart items to filter from collection
$_session= Mage::getSingleton('checkout/session');
$_cart_products = array();
foreach($_session->getQuote()->getAllItems() as $_item)
   $_cart_products[] = $_item->getProductId();

//merge product arrays to filter from collection
$_inuse_products = array_unique(array_merge($_related_products, $_cart_products));      

//getting collection
$_productCollection = Mage::getModel('catalog/product')
->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', null, 'left')
->addAttributeToFilter('entity_id', array('nin' => $_inuse_products))
->addAttributeToFilter('entity_id', array('neq' => $_current_prod->getId()))
->addAttributeToFilter('category_id', array(
    array('finset' => $_current_cat_id),
    array('finset' => $_parent_cat_id))

//sort collection = deepest category level first and limit it to 12
$_productCollection->getSelect()->order(new Zend_Db_Expr('FIELD(category_id, ' . implode(',', $_cat_order).')'))->limit(12);

I guess there is some to optimize? :)

  • 1
    I think using ->addCategoryFilter(Mage_Catalog_Model_Category) would be easier than doing the joins yourself. (do it twice if you want to do it for the parent aswell). You could maybe use Mage::getResourceModel('catalog/product_collection') instead of Mage::getModel('catalog/product')->getCollection() but I'm not sure wheter it's gonna make a big change or not, you'll have to figure it out yourself. Commented Jul 15, 2014 at 14:36

Put this to View.phtml it will work. just need to adjust the css

<?php $categories = $_product->getCategoryIds(); ?> 
<?php $result = array(); 
foreach($categories as $cat_id) { 
$category = Mage::getModel('catalog/category'); 
$collection = $category->getProductCollection(); 
foreach ($collection as $product) { 
$result[] = $product->getId(); 
<div class="box-others-also-like" style=" margin:0 auto;"> 
<?php if(sizeof($result) >= 10) 
$ourneed = array_rand($result,10); 
foreach($ourneed as $cc) 
$thisproduct= Mage::getModel('catalog/product')->load($result[$cc]); 
<li style=" border:1px solid #ccc; float:left; width:140px; margin:10px 5px 0 0; overflow:hidden;"> 
<a href="<?php echo $thisproduct->getProductUrl(); ?>" title="<?php echo $thisproduct->getName(); ?>" ><img src="<?php echo $this->helper('catalog/image')->init($thisproduct,'small_image')->resize(140) ?>" width="140" height="140" alt="<?php echo $thisproduct->getName(); ?>" /></a> 
<a href="<?php echo $thisproduct->getProductUrl(); ?>" title="<?php echo $thisproduct->getName(); ?>" title="<?php echo $thisproduct->getName(); ?>" >
<h3 style=" font:12px/1.55 arial; width:140px; height:4.25em; overflow:hidden;"><?php echo $thisproduct->getName(); ?></h3></a> 
<?php } ?> 
<?php }else { 
foreach($result as $cc) 
$thisproduct= Mage::getModel('catalog/product')->load($cc); 
<a href="<?php echo $thisproduct->getProductUrl(); ?>" title="<?php echo $thisproduct->getName(); ?>" ><img src="<?php echo $this->helper('catalog/image')->init($thisproduct, 'small_image')->resize(200) ?>" width="200" height="200" alt="<?php echo $thisproduct->getName(); ?>" /></a> </li> 
<?php } 
  • This is missing the "fill up with parent category products" and "sort by deepest category level first" functionality Commented Dec 18, 2014 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.