random parameter - $this->db->order_by();

[eluser]Unknown[/eluser]
Hi all,

I've hit a snag with Active Record trying to randomize data when doing a select:

Code:

$this->db->select('*');
$this->db->from('category_1');
$this->db->order_by('category_name', 'random');
            
$query = $this->db->get();

Generates the following statement (Which errors with mysql):

Code:

SELECT * FROM (`category_1`) ORDER BY `` RAND()

Code:

$this->db->order_by('', 'random');

gives the same error.

The only way I can get it to work is:

Code:

$this->db->order_by('RAND()')

But the manual doesn't explain it this way.

If I change line 854 in DB_active_rec.php from:

Code:

$orderby = ''; // Random results want or don't need a field name

To

Code:

$orderby = NULL; // Random results want or don't need a field name

It works as expected.

Thanks
Mark.

[eluser]barbazul[/eluser]
Confirmed bug!

You should report in the bug tracker.

[eluser]Derek Allard[/eluser]
Not sure if this is a bug or not. If you are ordering by random, then the results should be random should they not? I'm not sure it makes sense to say "field, and then random after that".

[eluser]barbazul[/eluser]
It loses track of the passed fields.
In the first example the expected result would be (in mysql):

Code:

SELECT * FROM (`category_1`) ORDER BY `category_name`, RAND()

It does make sense in some cases to sort by some fields and then randomize in the case of repeated values.
And besides, if that didn't make sense, then it doesn't make sense to pass the "random" keyword as the second parameter (that is AFTER the fields)

In any case I made a couple of more test cases and haven't been able to make mysql throw any error messages. What version of MySQL/PHP/CI are you running ?