CODEWITHSUNDEEP
phpmysqlkohana-3
Haradzieniec
Haradzieniec

Reputation: 9338

Get a random row from the table using Kohana3 framework - fast

How to make this code worked in Kohana3 framework? I have a syntax problem with it.

ORM::factory('table1')
->where ( 'id', '=', ceil( DB::expr('rand()')  * [SELECT max(id) from table1] ) )
->find();

That's how I want to select a random row from the table.

This works fine for small tables (that contains than 1000 rows), but not for big tables:

ORM::factory('table1')
->order_by(DB::expr('RAND()'))
->find();

The standard mysql equivalent of what I want is something like this:

SELECT name
  FROM table1 JOIN
       (SELECT CEIL(RAND() *
                    (SELECT MAX(id)
                       FROM table1)) AS id
        ) AS r2
       USING (id);

So, how to convert that into a working code for Kohana3 framework?

P.S. This method works fine if there are no holes (no deleted rows) in the table, and that's fine in my case.

Upvotes: 2

Views: 93

Answers (1)

Brent Baisley
Brent Baisley

Reputation: 12721

What you are trying to do is really a problem with MySQL. You need the right query to have it perform for any size table. A more scalable implementation is to use PHP to generate a random number less than the max ID and then select on that.

Get the highest ID:

SELECT MAX(id) max_id FROM table1

In PHP get the "id" to select:

$rand_id = mt_rand(0, $max_id-1);

Then create your query to select a random record, using a LIMIT 1 so it works even if there are holes.

SELECT * FROM table1 WHERE id>=$rand_id ORDER BY id LIMIT 1

Doing it all in MySQL can be done, but it's not going to be easy to read or implement with a DB abstraction layer.

Upvotes: 2

Related Questions