8

I'm using the ORM module in Kohana 3 and instead of displaying the first row of a database result set, what query should I use in my code when I want to get a random row from a certain table?

random
  • 9,774
  • 10
  • 66
  • 83
ed.
  • 83
  • 1
  • 4

2 Answers2

15

You can use this (if using MySQL):

ORM::factory('some_model')->order_by(DB::expr('RAND()'))->find();
dusan
  • 9,104
  • 3
  • 35
  • 55
8

You can issue the query directly, if you are using MySQL:

SELECT * FROM table LIMIT 1 ORDER BY RAND();

Or with Kohona Query Builder:

$this->db->from('table')->select('*')->limit(1)->orderby(null, 'RAND()')->get();
Alix Axel
  • 151,645
  • 95
  • 393
  • 500
  • If it is a much-used query I would even consider making it a proc since it requires no input. – Xeoncross Dec 24 '09 at 04:38
  • Oops, forgot to say that I'm using ORM. How do we do that using ORM? :) – ed. Dec 24 '09 at 04:55
  • @Xeoncross: Yeah, that may be a good idea. @ed: AFAIK you don't, ORMs aren't meant for that kind of stuff. – Alix Axel Dec 24 '09 at 05:33
  • your solution works, but only because second parametr in order_by instruction is not escaped or SQL-injection protected in other way (should be!). So "right" way to do this is thru Db:expr – hamczu Aug 17 '11 at 03:44