Laravel - Eloquent or Fluent random row
tl;dr: It's nowadays implemented into Laravel, see "edit 3" below.
Sadly, as of today there are some caveats with the ->orderBy(DB::raw('RAND()'))
proposed solution:
- It isn't DB-agnostic. e.g. SQLite and PostgreSQL use
RANDOM()
Even worse, this solution isn't applicable anymore since this change:
$direction = strtolower($direction) == 'asc' ? 'asc' : 'desc';
edit: Now you can use the orderByRaw() method: ->orderByRaw('RAND()')
. However this is still not DB-agnostic.
FWIW, CodeIgniter implements a special RANDOM
sorting direction, which is replaced with the correct grammar when building query. Also it seems to be fairly easy to implement. Looks like we have a candidate for improving Laravel :)
update: here is the issue about this on GitHub, and my pending pull request.
edit 2: Let's cut the chase. Since Laravel 5.1.18 you can add macros to the query builder:
use Illuminate\Database\Query\Builder;
Builder::macro('orderByRandom', function () {
$randomFunctions = [
'mysql' => 'RAND()',
'pgsql' => 'RANDOM()',
'sqlite' => 'RANDOM()',
'sqlsrv' => 'NEWID()',
];
$driver = $this->getConnection()->getDriverName();
return $this->orderByRaw($randomFunctions[$driver]);
});
Usage:
User::where('active', 1)->orderByRandom()->limit(10)->get();
DB::table('users')->where('active', 1)->orderByRandom()->limit(10)->get();
edit 3: Finally! Since Laravel 5.2.33 (changelog, PR #13642) you can use the native method inRandomOrder()
:
User::where('active', 1)->inRandomOrder()->limit(10)->get();
DB::table('users')->where('active', 1)->inRandomOrder()->limit(10)->get();
You can use:
ModelName::inRandomOrder()->first();
This works just fine,
$model=Model::all()->random(1)->first();
you can also change argument in random function to get more than one record.
Note: not recommended if you have huge data as this will fetch all rows first and then returns random value.
Laravel >= 5.2:
User::inRandomOrder()->get();
or to get the specific number of records
// 5 indicates the number of records
User::inRandomOrder()->limit(5)->get();
// get one random record
User::inRandomOrder()->first();
or using the random method for collections:
User::all()->random();
User::all()->random(10); // The amount of items you wish to receive
Laravel 4.2.7 - 5.1:
User::orderByRaw("RAND()")->get();
Laravel 4.0 - 4.2.6:
User::orderBy(DB::raw('RAND()'))->get();
Laravel 3:
User::order_by(DB::raw('RAND()'))->get();
Check this article on MySQL random rows. Laravel 5.2 supports this, for older version, there is no better solution then using RAW Queries.
edit 1: As mentioned by Double Gras, orderBy() doesn't allow anything else then ASC or DESC since this change. I updated my answer accordingly.
edit 2: Laravel 5.2 finally implements a wrapper function for this. It's called inRandomOrder().