How to Efficiently Retrieve Random Rows from MySQL in Laravel

Laravel often needs to fetch random rows from a MySQL database, for example to assign orders or sample users.

1. Using native SQL MySQL supports ORDER BY RAND() together with LIMIT to select a random subset. SELECT * FROM table WHERE name="" ORDER BY RAND() LIMIT 100; In Laravel you can run the same statement with DB::select():

$info = DB::select('SELECT * FROM table WHERE name="" ORDER BY RAND() LIMIT 100');

2. Using raw expressions Laravel’s query builder allows DB::raw('RAND()') or orderByRaw('RAND()') to achieve the same effect.

$info = self::where('dealing', '<>', '')

->orderBy(DB::raw('RAND()'))

->take(5)

->get();

3. Using the inRandomOrder method Laravel provides inRandomOrder() which automatically adds a random ordering clause.

$info = DB::table('users')

->inRandomOrder()

->take(5)

->get();

For the full article, click the “Read original” link below.