Magento 1.8 Product Collection – Random Collection Not Working

collection;magento-1.8product-collectionrandom

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();

Best Answer

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;
}

Related Solutions

Magento – How to Get 5 Random Product IDs from Product Collection

I found out that ORDER BY RAND() is very unperformant on a Magento collection because all data gets copied to a temp table, assigned a random number and then sorted without index. If we reduce the data to be copied to just the ID, it gets a lot faster (still not perfect because the slow sorting remains but OK for a small catalog like yours).

For this, I made a modified version of getAllIds():

$numberOfItems = 5;

// Step 1: Preselect ids using ORDER BY RAND()
// Filters are applied here
$productCollection = Mage::getModel('catalog/product')
    ->getCollection();
$productCollection
    ->addStoreFilter()
    ->setVisibility(Mage::getSingleton('catalog/product_visibility')->getVisibleInCatalogIds());
$productCollection->getSelect()->order('RAND()');
$idsSelect = clone $productCollection->getSelect();
$idsSelect->reset(Zend_Db_Select::LIMIT_COUNT);
$idsSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
$idsSelect->reset(Zend_Db_Select::COLUMNS);
$idsSelect->columns('e.' . $productCollection->getEntity()->getIdFieldName());
$idsSelect->limit($numberOfItems, null);
$idsSelect->resetJoinLeft();
$accessor = new ReflectionObject($productCollection);
$_bindParams = $accessor->getProperty('_bindParams');
$_bindParams->setAccessible(true);
$chosenIds = $productCollection->getConnection()
    ->fetchCol($idsSelect, $_bindParams->getValue($productCollection));

Then I could load the products by these random ids:

// Step 2: Load products
// Attributes and index data are joined here
$productCollection->addIdFilter($chosenIds);
$productCollection
    ->addMinimalPrice()
    ->addFinalPrice()
    ->addTaxPercents()
    ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
    ->addUrlRewrite();

$productCollection->load();

Simpler alternative

An alternative that also works well on small catalogs (<10000 products) is to load all ids and select the random IDs with PHP:

$numberOfItems = 5;

$productCollection = Mage::getModel('catalog/product')->getCollection();
$productCollection
    ->addStoreFilter()
    ->setVisibility(Mage::getSingleton('catalog/product_visibility')->getVisibleInCatalogIds());
$candidateIds = $productCollection->getAllIds();
$numberOfProducts = count($candidateIds);
$chosenIds = [];
while ($numberOfItems) {
    $randomKey = mt_rand(0, $numberOfProducts - 1);
    if (!isset($chosenIds[$randomKey])) {
        chosenIds[$randomKey] = $candidateIds[$randomKey];
        --$numberOfItems;
    }
}
$productCollection->addIdFilter($chosenIds);
$productCollection
    ->addMinimalPrice()
    ->addFinalPrice()
    ->addTaxPercents()
    ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
    ->addUrlRewrite();

$productCollection->load();

You can read a more in depth analysis and see some benchmarks in my blog: http://www.schmengler-se.de/en/2015/09/show-random-products-in-magento-you-are-doing-it-wrong/

Magento – Getting custom attribute value from simple product load based on product collection

Have you looked at the prototype for getUsedProducts? It's public function getUsedProducts($requiredAttributeIds = null, $product = null) so you can call getUsedProducts(array(id_of_your_attribute), $product)

Looping through that array and then loading each child is probably not the most efficient way of doing what you're after. You could either add the other attributes you need to the getUsedProducts, or if there's lots and adding by id doesn't make sense, just use getUsedProductCollection and add call addAttributeToSelect or addAttributeToFilter yourself, see what getUsedProducts does itself:

if (is_array($requiredAttributeIds)) {
    foreach ($requiredAttributeIds as $attributeId) {
        $attribute = $this->getAttributeById($attributeId, $product);
        if (!is_null($attribute))
            $collection->addAttributeToFilter($attribute->getAttributeCode(), array('notnull'=>1));
    }
}

Best Answer

Related Solutions

Magento – How to Get 5 Random Product IDs from Product Collection

Simpler alternative

Magento – Getting custom attribute value from simple product load based on product collection

Related Topic