Jump to content

ORDER BY RAND() pagination issue


1internet

Recommended Posts

So I want to show my results in random order, I have used the ORDER BY RAND() mysql function, but the issue is, when you have pagination and go to another page, every page is also random results, i.e. there could be the same results as the previous page, and by clicking through all pages, you will never see all results.

My query is

SELECT * FROM `businesses` ORDER BY RAND() LIMIT $startResults, $perPage

 

So how do I make all the results random, but not sure duplicate results on the other pages?

Link to comment
Share on other sites

You have to save the results of the entire query to another (temporary) location, and use that for the basis of the pagination.

The simplest way you could do this, is to just serialize () the array with the results from the query (without the LIMIT clause), and then save it to a file or temporary table. However you control access to that file/table, say one per user, for instance, or how long it should be available, is completely up to you.

 

There are also other, more complicated, methods to solve this. Methods which may be more suited to the task if we're talking about a lot of data, but generally this should serve pretty well.

Edited by Christian F.
Link to comment
Share on other sites

RAND() allows you to seed the generator. Pick a random number, save that somewhere, and use it every time.

$query = "SELECT * FROM `businesses` ORDER BY RAND($random) LIMIT $startResults, $perPage";

 

[edit] This is fine if you don't have too many results; this query is bad and will take a relatively long time so if not then storing the results in a table (with some identifying information so you know whose results they are) is better.

Edited by requinix
Link to comment
Share on other sites

Take the results in an array. An use array pagination it will work

Unless there are 1M rows to consider. Or even 10K.

 

[edit] Shuffling the array, which you didn't say but would be a fundamental requirement to the solution, doesn't help. It's the same problem: it'll be random every single time. srand() might affect shuffle(), I don't know, but if so that would be a solution to that... except if you're seeding the RNG you might as well do it in MySQL where it'll perform better.

Edited by requinix
Link to comment
Share on other sites

This is really more complicated than you think. If the pagination order is supposed to be random how would it handle new/removed records?

 

So, I'm on page 2 and someone removes a record that would have been on page 3. What happens when I navigate to Page 3? Depending on the method chosen above, the result could be all over the place. Page 3 has 1 less record than it should, the entire record set could be completely re-randomized (even with a seed value), empty row int he grid, etc. etc. And the same problems could arise if someone adds a record.

Link to comment
Share on other sites

Not quite seeing the problem you're describing, Psycho. Either method described would pull the full number of results on page 3, the only difference would be whether or not you'd see the deleted record as well. Even if he went for the temporary storage solution, he could still write code that deleted the record from there as well.

The ordering of the results aren't of any consequence for the number of rows the query returns, after all. Not unless you save the page number for each record on the first query. Which, I agree, would be quite silly. ;)

Link to comment
Share on other sites

Not quite seeing the problem you're describing, Psycho. Either method described would pull the full number of results on page 3, the only difference would be whether or not you'd see the deleted record as well.

If you do the seeded-RAND() I mentioned then the order will change a bit: rows located earlier that the deleted record will sort the same relative to each other, but rows after the deleted record will appear randomly interspersed. At least in MySQL 5.5.

mysql> create table test (a int);
Query OK, 0 rows affected (0.12 sec)
 
mysql> insert into test values (1),(2),(3),(4),(5),(6),(7),(,(9),(10);
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0
 
mysql> select * from test order by rand(123);
+------+
| a    |
+------+
|    6 |
|    4 |
|    9 |
|    8 |
|    2 |
|   10 |
|    3 |
|    5 |
|    1 |
|    7 |
+------+
10 rows in set (0.06 sec)
 
mysql> delete from test where a=10;
Query OK, 1 row affected (0.03 sec)
 
mysql> select * from test order by rand(123);
+------+
| a    |
+------+
|    6 |
|    4 |
|    9 |
|    8 |
|    2 |
|    3 |
|    5 |
|    1 |
|    7 |
+------+
9 rows in set (0.03 sec)
 
mysql> delete from test where a=5;
Query OK, 1 row affected (0.04 sec)
 
mysql> select * from test order by rand(123);
+------+
| a    |
+------+
|    7 |
|    4 |
|    9 |
|    2 |
|    3 |
|    6 |
|    1 |
|    8 |
+------+
8 rows in set (0.04 sec)

Link to comment
Share on other sites

If you do the seeded-RAND() I mentioned then the order will change a bit: rows located earlier that the deleted record will sort the same relative to each other, but rows after the deleted record will appear randomly interspersed. At least in MySQL 5.5.

 

Right. What I was getting at is that if the order is "random" there is no logical way to handle adds/deletions to the source data as you navigate from page to page. So, if using the seed method, a record was deleted from page 2, then all the records on page 3, 4, etc. would change.

 

You could build some custom functionality such as getting a random list and storing it is session/cookie data. Then, if a record is deleted, all the records after that record would move up in position. And, if records are added, the only logical thing to do would be to add them to the end of the list (which really isn't random). If you are going to create some fixed list to start such as this, you definitely need to account for add/deletes otherwise you may get less records than intended on a page and new records won't display in the data.

 

Believe me, I have had to deal with many different issues regarding pagination. I've had product managers and designers come up with plenty of ideas that sound good on paper but cannot logically be implemented (e.g. subtotals). When these issues are identified the typical response is to add more complexity to handle those issues - which leads to more bugs. Some may argue that add/deletes while a user is paginating through the records is an edge case scenario and does not need to have a lot of additional work to make it "perfect". However, my position is that whatever feature is being implemented should "just work". A randomized pagination doesn't "just work". And, personally, as a user, my first instinct would be to sort the grid in some logical order - but that my be due to the fact that I have a slight OCD tendency.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.