4

This code is not working

class My_Module_Block_New_New extends Mage_Catalog_Block_Product_List {
...
protected function _getProductCollection()
{
    if (is_null($this->_productCollection)) {
        $collection = Mage::getResourceModel('catalog/product_collection');
        Mage::getModel('catalog/layer')->prepareProductCollection($collection);

        $collection->addAttributeToFilter('newproduct', 1)
                   ->setPage(1, $this->getProductsCount())->load()
                   ->addStoreFilter()
                   ->getSelect()->order(new Zend_Db_Expr('RAND()'));

        echo $collection->getSelect();

        $this->_productCollection = $collection;
    }
    return $this->_productCollection;
}

echo $collection->getSelect();

SELECT 1 AS `status`, `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `e`.`name`, `e`.`description`, `e`.`short_description`, `e`.`price`, `e`.`special_price`, `e`.`special_from_date`, `e`.`special_to_date`, `e`.`weight`, `e`.`manufacturer`, `e`.`manufacturer_value`, `e`.`small_image`, `e`.`thumbnail`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`tax_class_id`, `e`.`url_key`, `e`.`required_options`, `e`.`image_label`, `e`.`small_image_label`, `e`.`thumbnail_label`, `e`.`price_type`, `e`.`weight_type`, `e`.`price_view`, `e`.`shipment_type`, `e`.`links_purchased_separately`, `e`.`links_exist`, `e`.`is_imported`, `e`.`gammes`, `e`.`gammes_value`, `e`.`ean`, `e`.`delivery_time`, `e`.`inchoo_featured_product`, `e`.`newproduct`, `e`.`coup_de_coeur`, `e`.`contenance`, `e`.`prix_salon`, `e`.`promo_du_mois`, `e`.`volume_weight`, `e`.`code_video`, `e`.`msrp_enabled`, `e`.`msrp_display_actual_price_type`, `e`.`msrp`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `cat_index`.`position` AS `cat_index_position` FROM `catalog_product_flat_1` AS `e` INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '3' WHERE (e.newproduct = '1') ORDER BY RAND() LIMIT 3

Based on others topics getSelect()->order(new Zend_Db_Expr('RAND()')) should work but not in my case, what am i doing wrong ?!

Thx

EDIT This code is working

$collection->getSelect()->order('rand()');
$collection->addStoreFilter();
$collection->addAttributeToFilter('newproduct', 1);
$collection->setPage(1, $this->getProductsCount())->load();

2 Answers 2

3

Please try below code:

protected function _getProductCollection()
{
    if (is_null($this->_productCollection)) {
        $collection = Mage::getResourceModel('catalog/product_collection')->addAttributeToFilter('newproduct', 1);
        Mage::getModel('catalog/layer')->prepareProductCollection($collection);
        $collection->getSelect()->order('rand()');
        $collection->addStoreFilter();

    $numProducts = $this->getProductsCount()?$this->getProductsCount():0
    $collection->setPage(1, $numProducts);

        $this->_productCollection = $collection;
    }
    return $this->_productCollection;
}
3
  • 1
    your solution is working. So in the end i had to put getSelect())>order('rand()'); at the first position and also syntaxe error (use $collection->...; at each row)
    – Lanks
    Feb 6, 2015 at 15:15
  • 1
    Don't use ORDER BY RAND() on a large catalog, it has performance issues Feb 6, 2015 at 15:34
  • yes,i have agree with you...
    – Amit Bera
    Feb 6, 2015 at 15:35
1

Performant Solution Without ORDER BY RAND()

ORDER BY RAND() is unperformant 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.

protected function _getProductCollection()
{
    $productCollection = Mage::getModel('catalog/product')->getCollection();
    $productCollection
        ->addStoreFilter()
        ->setVisibility(Mage::getSingleton('catalog/product_visibility')->getVisibleInCatalogIds());

    $numberOfItems = $this->getProductCount();
    $candidateIds = $productCollection->getAllIds();
    $choosenIds = [];
    $maxKey = count($candidateIds)-1;
    while (count($choosenIds) < $numberOfItems)) {
        $randomKey = mt_rand(0, $maxKey);
        $choosenIds[$randomKey] = $candidateIds[$randomKey];
    }    
    $productCollection->addIdFilter($choosenIds);

    $productCollection
        ->addMinimalPrice()
        ->addFinalPrice()
        ->addTaxPercents()
        ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
        ->addUrlRewrite();

    return $productCollection;
}

See also: https://magento.stackexchange.com/a/78694/243

Or 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/:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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