magento get products from category, order by rand()

Question

I have the following:

$products = Mage::getModel('catalog/product')
    ->getCollection()
    ->addAttributeToSort('id', 'RAND()')
    ->addAttributeToSelect('small_image')
    ->addCategoryFilter(Mage::getModel('catalog/category')->load($catId));

But I need to order by id RAND(), how can I do this? (The code shows how I've tried with no luck)

Solution 1

Magento collection do not accept parameters other then one of selected attribute. In this case you should get Zend_Db_Select object and add order instruction to it.

$products = Mage::getModel('catalog/product')
    ->getCollection()
    ->addAttributeToSort()
    ->addAttributeToSelect('small_image')
    ->addCategoryFilter(Mage::getModel('catalog/category')->load());
$products->getSelect()->order(new Zend_Db_Expr('RAND()'));

To see what query will be executed you may use this construnction

$products->load(true, true); // first parameter show sql query in output, second show sql query in var/log/syslog

Solution 2

Refer to this question: query magento limit + order by rand() and clockworkgeek's answer:

$collection->getSelect()->order(new Zend_Db_Expr('RAND()'));

Solution 3

Using ORDER BY RAND() to return a list of items in a random order will require a full table scan and sort. It can negatively affect performance on large number of rows in the table.

There are several alternative solutions possible of how to optimize this query. Magento provides a native solution for that.

The orderRand() method of Varien_Db_Select and the database adapter allows to specify a random order and leverage index for ORDER BY. Specify a name of some integer indexed column to be used in the ORDER BY clause, for example:

$collection->getSelect()->orderRand('main_table.entity_id');

See Varien_Db_Adapter_Pdo_Mysql::orderRand() for implementation details.