Order by rand() in yii2

Question

I want to write following query in yii2, but I can't get the expected output:

SELECT * FROM user where category_id=5 ORDER BY rand() LIMIT 4

For that I have done following:

$data= User::find()->where(['category_id'=> 5])->orderBy(['rand()'])->limit(4);

But it generates the command like following

SELECT * FROM `user` WHERE `category_id`=5 ORDER BY `0` LIMIT 4

Which is not a valid mysql statement,so what should I do to get the query right?

My aim is to get any random 4 records from user table.

Answer 1

Wrap it into yii\db\Expression to prevent escaping and remove array part:

use yii\db\Expression;

...

$query = User::find()
    ->where(['category_id' => 5])
    ->orderBy(new Expression('rand()'))
    ->limit(4);

Answer 2

I'm not really an expert on yii2 query builder, however, specify the ordering should help

$data= User::find()->where(['category_id'=> 5])->orderBy(['rand()' => SORT_DESC])->limit(4);