Randomize Active Record Results

[eluser]jpschroeder[/eluser]
Hi,

It seems lately there has been a flurry of articles whizzing around warning that MySQL's RAND() is the devil, particularly on large tables. Thats fine, but I was planning on using that to retrieve a random result from a large table in MySQL using CI:

Code:

$this->db->order_by("id","random");

So instead I'm left to do the randomization on the PHP side. I know how I could get this done the dirty way, but it would involve returning an array, and randomizing that. A lot of code downstream has already been written to handle the result object that is returned.

How would I take the result and randomize its order?

Code:

_ID_________FRUIT_________
1      | "apple"          |
2      | "pear"           |
3      | "berries"        |
...                       |
100000 | "blueberries"    |
__________________________|

$this->db->limit(10);
$query = $this->db->get("fruit");

// post query randomize...???

Thanks!

[eluser]weboap[/eluser]
hope this help
you can use random_element()
http://ellislab.com/codeigniter/user-gui...elper.html


or

http://stackoverflow.com/questions/41027...-key-value
http://php.net/manual/en/function.shuffle.php

the result need to be returned as an array thought

Code:

return $query->result_array();

[eluser]jpschroeder[/eluser]
Yes this is the way imagined it could be done as well. However I'm trying to see if anyone knows a way to do this without returning as an array or converting to an array. That way I don't have to re-write the code down stream

[eluser]PhilTem[/eluser]
I'm not sure if I got your question, right: Do you want to randomize the rows you retrieve from the database, or do you want to first get a set of data from the DB and then randomize this?

For the first case, you can of course use MySQL's RAND() but you may also create a set of 10 random integers serving as primary keys and only grab these. Getting that random primary keys array is rather simple with PHP
Of course you need to ensure, that the randomly generated keys are valid keys and that you don't have any keys twice or three times, but as just mentioned, it's rather simple.

The second one might be similar to the first one, i.e. you have your query result handle $query and then just use a random number n (or 10) to retrieve the n-th row of your results like

Code:

$row = $query->row($random_number); // for object
$row = $query->row_array($random_number); // for array

Maybe I'm not far off what you were aiming to get and hopefully it will lead you into the right direction.

PS: None of the ways mentioned above changes the type of data you get from the DB, i.e. it's still an array or an object, depending on how you want it.

[eluser]jpschroeder[/eluser]
@PhilTem What you are propsing will work. I could run something of this sort:

Code:

$query = $this->db->get($table);
$results = range(0,$query->num_rows()-1);
shuffle($results);
foreach($results as $row_num){
   $row = $query->row($row_num);
}

I'm just being picky at this point, but I was hoping to do something like the following:

Code:

$query = $this->db->get($table);
$this->db->shuffle($query);
....
//move on with output

Unless someone knows how do do something like that, consider this post solved! Thanks!

[eluser]Aken[/eluser]
The DB methods that return more than one result will ALWAYS return an array. $query->result() will return an array of objects. $query->result_array() will return an array of arrays. If you're looking to keep each row as an object, all you have to do is randomize the $query->result() array, which is as simple as:

Code:

$query = $this->db->get($table);
$rows = $query->result();

// Pull one random item.
$random = $rows[array_rand($rows)];

// Randomize the entire result array.
shuffle($rows);

If you want to do that in a DB method like your example, then search around for extending the database and add it yourself!