query magento limit + order by rand()

function getIdModelsSliderJuwels(){
 $collection = Mage::getModel("catalog/product")->getCollection();
 $collection->addAttributeToFilter("attribute_set_id", 27); 
     $collection->addAttributeToS开发者_运维知识库elect('modellijnen'); 
   //  $collection->setRandomOrder();
   //  $collection->getSelect()->limit( 5 ); 
 return $collection; 
}

Hi there,

I'd like to know how to set a limit to your query running in Magento because $collection->getSelect()->limit( 5 ); doesn't work.

Also how to select randomly, $collection->setRandomOrder(); also doesn't work.

txs.

setRandomOrder does not work for collections of products, only for related products. You'll have to add it yourself with this code:

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

A shortcut for setting both page size and number at the same time is:

$collection->setPage($pageNum, $pageSize);

As clockworkgeek said, use the $collection->getSelect()->order(...) method to randomize the order. To limit it to just $n number of items you can also use

$collection->getSelect()->limit($n);

try to use

$collection->setPageSize(5)->setCurPage(1);

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.