Order By Rand() And Total Posts

Hi,

I want to select 10 users with the most posts. But in random order.

Of course this won’t work, what should I change?




		$criteria = new CDbCriteria();	

		$criteria->limit = 10;

		$criteria->order = 'total_posts ASC, RAND()';

		

		$users = User::model()->findAll($criteria);



Thanks in advance!

First of all, you need to use desc instead of asc.

Next. it’s much simpler to use php instead of pure-sql solution:


$criteria = new CDbCriteria();  

$criteria->limit = 10;

$criteria->order = 'total_posts DESC';

                

$users = User::model()->findAll($criteria);

shuffle($users):

Btw never ever use ‘order by rand()’ on large sets of data.

There are a lot of workarounds for this.

Indeed DESC, it was a type in my example.

Thanks a lot. Didn’t know it was that easy with the shuffle() function.

So ORDER BY RAND() is not smart to use you mean? Because of performance I guess?

Thank anyway, you helped me out again!

In case of large datasets - yes, because it will cause using temporary + using filesort, which is not good for performance and memory.

Small datasets (like “select * from table where (some condition giving 10 rows) order by rand()”) is ok. Anyway, do not forget to use EXPLAIN for your queries (query planner drives me crazy sometimes), and don’t hesitate to use workarounds in cases when your DB is doing heavy non-DB job.