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.

Unless you don't care about performance, never order by `rand()`. It's [terribly inefficient](https://www.warpconduit.net/2011/03/23/selecting-a-random-record-using-mysql-benchmark-results/). — Beowulfenator, Nov 18 '15 at 00:07
@Beowulfenator got it thank you. I used the method in the conclusion on that article. — Mike Ross, Nov 18 '15 at 00:11
Read the article. There are different solutions there and a graph that shows how they perform on different data sets. It is not relevant how many records you need to find, it's more important how many total entries you have in the table. — Beowulfenator, Nov 18 '15 at 00:15

arogachev · Accepted Answer · 2016-02-08T16:58:26.033

32

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

edited Feb 08 '16 at 16:58

answered Nov 17 '15 at 03:08

arogachev

thats nice. Its working perfectly. And can you help me with the `Limit` not working in `ActiveDataProvider` part? what am i doing wrong there? – Mike Ross Nov 17 '15 at 03:14
That's different question, the current problem is solved. – arogachev Nov 17 '15 at 03:15
1

for limit @arogachev provide an answer to another question this is the link [link](http://stackoverflow.com/questions/33748211/limit-is-not-working-in-activedataprovider) – Amitesh Kumar Nov 17 '15 at 04:29
1

please change Expresssion('rand()') to Expression('rand()') – sj59 Feb 08 '16 at 13:38
@sj59 Done, thanks for remark. By the way, you can suggest edit for any post too. – arogachev Feb 08 '16 at 16:59

score 4 · Answer 2 · answered Nov 17 '15 at 00:44

4

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

answered Nov 17 '15 at 00:44

Shadow

spot on. That was the problem but still the `limit` doesnt work in `ActiveDataProvider`. Any thoughts? – Mike Ross Nov 17 '15 at 00:50
just set pagination to false and then manual setting of limit will work – Amitesh Kumar Nov 17 '15 at 04:31

2 Answers2