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.