Laravel - Eloquent or Fluent random row

How can I select a random row using Eloquent or Fluent in Laravel framework?

I know that by using SQL, you can do order by RAND(). However, I would like to get the random row without doing a count on the number of records prior to the initial query.

Any ideas?

18 Answers

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().

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.

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(); 

In Laravel 4 and 5 the order_by is replaced by orderBy

So, it should be:

User::orderBy(DB::raw('RAND()'))->get(); 

it's very simple just check your laravel version

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(); 

You can also use order_by method with fluent and eloquent like as:

Posts::where_status(1)->order_by(DB::raw(''),DB::raw('RAND()')); 

This is a little bit weird usage, but works.

Edit: As @Alex said, this usage is cleaner and also works:

Posts::where_status(1)->order_by(DB::raw('RAND()')); 

For Laravel 5.2 >=

use the Eloquent method:

inRandomOrder() 

The inRandomOrder method may be used to sort the query results randomly. For example, you may use this method to fetch a random user:

$randomUser = DB::table('users') ->inRandomOrder() ->first(); 

from docs:

You can easily Use this command:

// Question : name of Model
// take 10 rows from DB In shuffle records...

$questions = Question::orderByRaw('RAND()')->take(10)->get(); 

Use Laravel function

ModelName::inRandomOrder()->first(); 

I prefer to specify first or fail:

$collection = YourModelName::inRandomOrder() ->firstOrFail(); 

Laravel has a built-in method to shuffle the order of the results.

Here is a quote from the documentation:

shuffle() 

The shuffle method randomly shuffles the items in the collection:

$collection = collect([1, 2, 3, 4, 5]); $shuffled = $collection->shuffle(); $shuffled->all(); // [3, 2, 5, 1, 4] - (generated randomly) 

You can see the documentation here.

At your model add this:

public function scopeRandomize($query, $limit = 3, $exclude = []) { $query = $query->whereRaw('RAND()<(SELECT ((?/COUNT(*))*10) FROM `products`)', [$limit])->orderByRaw('RAND()')->limit($limit); if (!empty($exclude)) { $query = $query->whereNotIn('id', $exclude); } return $query; } 

then at route/controller

$data = YourModel::randomize(8)->get(); 

There is also whereRaw('RAND()') which does the same, you can then chain ->get() or ->first() or even go crazy and add ->paginate(int).

I have table with thousands of records, so I need something fast. This is my code for pseudo random row:

// count all rows with flag active = 1 $count = MyModel::where('active', '=', '1')->count(); // get random id $random_id = rand(1, $count - 1); // get first record after random id $data = MyModel::where('active', '=', '1')->where('id', '>', $random_id)->take(1)->first(); 

Try this code! It Works:

 User::orderBy(DB::raw('RAND()'))->get(); 

Here's how I get random results in eloquent in one of my projects:

$products = Product::inRandomOrder()->limit(10); 

10 - The number of random records to pull.

In Laravel 7.x and above, you can just do:

$data = Images::all()->random(4);