DataProvider, pagination and order by RAND()

Yii 1.1 General Discussion
1

I am using CSqlDataProvider and CListView to show a gallery of participants in a contest. In order to not give advantage to any of them, the first criteria for showing is random. For this, I add ORDER BY RAND() in the query that I run with CSqlDataProvider.

This is working fine for the first page of results, but when I browse to another page, it’s making a random selection again. Is there any way to avoid this?

2

This is a limitation of your database system. You want randomness on your first page request, but it should keep that same randomness on the second page request. The database can’t provide that as both queries are issued separately.

3

When does the CSqlDataProvider run the query exactly?

Would it be possible to create the CSqlDataProvider instance, store it in session (or another place), and then in the view check if it exists before trying to load the variable that has passed the controller? Something like:




// view code

'dataProvider' => Yii::app()->session['dp'] ? Yii::app()->session['dp'] : $concursantes,
4

On every page request a new query is performed. It doesn’t help to store CSqlDataProvider in session - it’s the database that needs to know your desired sort order.

Some notes what you could try, [color="#FF0000"]if you don’t have too many entries in that table[/color] (never did this myself, though):

  1. For every new visitor: Fetch all id’s from that table, shuffle them and store this shuffled list in user session

  2. Add condition ‘id IN (15,377,34,…)’ to your SQL

  3. Add ‘ORDER BY FIELD(id,15,377,34,…)’ to your SQL

This tells your database exactly which id’s you want to retrieve and in which order you want them. The list is generated from your complete list of ids from step1. So the non-trivial part (well, it’s maybe not so non-trivial after all ;) ) is to inspect the requested page from pagination and cut out the current page’s id’s from the complete shuffled list you have in user session.

5

I know this was a while ago, but I found these two pages which seem to offer a solution. Since I can’t post links yet do a google search for ‘is there any way to paginate when you order by rand()’ and the first page at wordpress.stackexchange.com and the sixth at nyrodev.info provide a solution.

6

Thank you! You saved me!

I have based my solution on this:

Cool solution

It works really good but you need to watch 2 things:

  1. Of course you have to be able to modify your table (think in legacy system, maybe you can’t do this). You only need to ADD a column to a table so, I think is safer.

  2. Because you need to UPDATE all your records each time you want to "randomize", it could be a really hard-to-solve performance issue (in my case this SQL sentence take less than 0.2 ms so is not a problem)

My solution:

a) I have added a "random_col" integer column to the table

B) In the action that receives the submit search request I set a session var “newrecord” to ‘true’ when is a POST request. It will say to method search in the model that this is a ‘fresh search’

c) In search method in the model I put a if sentence like this:




Yii::log('Performing search','trace','devel');

if(Yii::app()->session['newsearch']){

  $command=Yii::app()->db->createCommand("UPDATE the_table SET random_col = (FLOOR(1 + RAND() * 1000000));");

  $command->execute();

  Yii::log('Fresh search. Randomizing records','trace','devel');

}

Yii::app()->session['newsearch'] = false;

That way select over pagination don’t update random_col but new searches do it.

Hope it be helpfull

P.D.: Thanks Accelm for the clue