DerekAllard.com

Ordering Database results by “random” in CodeIgniter

A recent CodeIgniter bug report had got me looking into the depths of the database results functions of the framework.  Essentially, the orderby() function of CI’s Active Record says that you can sort by ASC (ascending), DESC (descending) or RAND().  Imagine this:

$query "SELECT * FROM table ORDER BY RAND()"

Anyone familiar with PHP probably looks and that and thinks of the native PHP rand() function.  This is pretty neat actually, and I’d never really thought about randomly ordering things.  But on further investigation, it became clear that this code wasn’t as nice as it initially seemed.  Firstly, its non-standard SQL, reducing its portability greatly.  While MySQL uses RAND(), other databases have their own way of doing things:

So then, how would one randomly order their database results? The easiest solution is probably to use a combination of CodeIgniter’s result_array() function, that returns your results into an array (surprised?) and my new favourite PHP function, shuffle(), which I seem to be finding a lot of use for lately.

Consider this:

$this->db->select('name');
$query $this->db->get('table');
$shuffled_query $query->result_array();
shuffle ($shuffled_query);

foreach (
$shuffled_query as $row{
    
echo $row['name''<br />';

In PHP 5, you could even chain some of these together and save yourself a line or two:

$shuffled_query $this->db->get('table')->result_array();
shuffle ($shuffled_query); 

I played with trying to extend the database library to allow for this (opps, can’t extend the database libraries), then writing a quick helper, then finally realized that its only a few lines of code, and is probably something a developer should just do for themselves.  For CodeIgniter to handle this random order, we could abstract out the “random” order into each database library that CI supports.  At some point in the future that may happen, but it isn’t even on the radar for the time being.

One notable point about this is that you have to use the array notation for your results ($row[‘field’]) instead of my preferred object notation ($row->field); but this seems a small price to pay.  In theory, one could write a function to remap the array into an object, but I’d be disinclined to do that much extra work for the sake of appearances.

Now the question… why would anyone want to randomize results?  Well, I can see applications in drawing “random comments” from a blog, “articles of the moment” from a database of writing, and in fact several other day to day uses.

edit: through a totally stupid action on my own part, I lost the last half of this article, and it needed a quick re-write.  While it still has most of the content of the original, it doesn’t retain any of my wonderful vocabulary or insightful wit… ;)  This is probably the third time I’ve done this on my blog… is anyone out there feeling my pain?

Comments

Eric wrote on

I read on but didn’t see your solution. :) 

I would probably use some thing like:
$this->db->orderby(“title”, “rand”);

Then in the active record helper look for the string rand and use the proper SQL.

John wrote on

If i’m writing a fair old chunk of something, i’ll ctrl+a then ctrl+c (replce ctrl with mac key for some) every now and then as i’m so used to losing stuff.

Exactly the same reason why i save word or excel docs every five minutes.

I trust no-one and no-thing these days, they’re all out to get me.

Eric Barnes wrote on

Glad you got it sorted and I do have to admit I like your idea better than my first thought. It seems easier and a lot less headache to introduce. :D

JOn wrote on

$shuffled_query $this->db->get('table')->result_array();
shuffle ($shuffled_query); 

How about this?:

$shuffled_query shuffle($this->db->get('table')->result_array()); 

Brevity :)

Derek wrote on

@JOn: unfortunately that code won’t work ;(

Message: Invalid argument supplied for foreach()

bummer… but don’t think I hadn’t tried it!

Jeremy D. Moore wrote on

Great idea and example!  Unfortunately, if you only wanted a partial amount of random results, being anything from 1 to (count($query->result_array()) - 1), using PHP’s shuffle(), you would still have to SELECT * to get a true random making for a lot of waste depending on how many rows you didn’t need afterwards.  Otherwise, you could get some random numbers in the domain of your ID range of the entries in your database using PHP and pull those specific rows out.  But it seems as though SQL standards should adopt a better solution.

Derek wrote on

Since the “randomizing” of the results happens after they’re back from the database, there is no reason you couldn’t simply use limit and where criteria to grab partial results, and then randomize them after the fact.

Am I “picking up what you’re putting down” Jeremy?

Jeremy D. Moore wrote on

LOL.  For some reason I had mistaken “portability” with “probability” in that you were suggesting that it was much like a random function that had not been seeded.  My fault… :)

garrett wrote on

Won’t pulling all the entries from the database and then displaying a random result create more load (and be slower) than pulling a single random result from the DB?

Champs wrote on

Yeah, isn’t this what an abstract database class for?

I use ORDER BY RAND() LIMIT 1 on a semi-regular basis, just for fun.

Derek wrote on

I suppose any extra processing will create more load, but yes, it would be less efficient then doing this in SQL.

isn’t this what an abstract database class for?

Was what I tried to address with

For CodeIgniter to handle this random order, we could abstract out the “random” order into each database library that CI supports.  At some point in the future that may happen, but it isn’t even on the radar for the time being.

It isn’t on the radar right now because all the current development work is going into ExpressionEngine 2, but I agree that if this was something that belonged in a framework, the best idea would be to abstract it out.

@Champs, your idea of fun would be scary to most anyone else ;)

Rafael wrote on

greatings commander… nice post!

just to know
ca.php.net/function, br.php.net/function, anything.php.net/function, aponts to same place in php.net (in my case, portuguese version). Although with marcians don’t work :)

For your pain, put an auto save post :P