Laravel - Eloquent or Fluent random row

Question

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?

There is no best way to do this without executing at least two queries. — NARKOZ, Dec 18 '12 at 07:19

score 863 · Accepted Answer · edited Aug 20 '20 at 13:45

863

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

edited Aug 20 '20 at 13:45

D Malan

10,272
3
25
50

answered Dec 18 '12 at 11:11

aebersold

11,286
2
20
29

91

Replace 'get' with 'first' if you want a single row. – Collin Price Feb 20 '14 at 19:11
16

for PostgreSQL use `'RANDOM()'` – dwenaus Nov 20 '14 at 16:01
4

Warning: on large datasets this is very slow, adding around 900 ms for me – S.. Jan 04 '15 at 22:34
1

This is a depends how the RDBMS implements RAND, on MySQL all rows are fetched and then one is picked. Which is pretty slow on big datasets. – aebersold Jan 05 '15 at 09:49
3

Can we paginate this? – Irfandi D. Vendy Sep 22 '15 at 10:03
3

You can, however the sorting will be random on every new page. Which makes no sense because it's essentially the same as you press F5. – aebersold Sep 22 '15 at 19:08
yeah , i used `User::orderBy(DB::raw('RAND()'))->get();` form my models, is the best for me – yussan Jun 03 '16 at 16:50
DB::raw('RAND()') slows down the query too much – CodeGuru Feb 23 '19 at 08:51
1

Caution, `User::all()->random();` will be slow on large datasets, prefer using `User::inRandomOrder()->get();`. As it will load ALL of your models in a php collection and then pick a random item. You may prefer a database approach without loading all your models. – Ifnot Jan 24 '20 at 12:36
DB::.... solution will return you "Class 'App\Http\Controllers\DB' not found" error so you will have to include DB as "use DB;" above your blablaController class code. Thanks a lot. – Kamlesh Apr 30 '21 at 05:01
Is there any way to randomised based on specific column. Using inRandomOrder() its randoming based on id. I need to randomised my data based on Zip Code. Is there any way to do that? – zia sultan Aug 23 '21 at 20:17

score 59 · Answer 2 · edited Jun 07 '19 at 01:15

59

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.

edited Jun 07 '19 at 01:15

Yevgeniy Afanasyev

37,872
26
173
191

answered Mar 05 '14 at 21:11

Manish

1,946
2
24
36

80

A performance-wise downside is that all the records are retrieved. – Gras Double Jul 21 '14 at 23:20
6

here random is called on the collection object not the sql query. the random function is run on php side – astroanu Oct 15 '15 at 06:13
@astroanu Right, but to populate that collection, all rows are queried. – MetalFrog Nov 14 '15 at 01:08
1

I could be wrong, but this doesn't seem to work when the parameter passed to the random function is the same as the size of the collection. – Brynn Bateman Jan 25 '16 at 18:08
This is not good... This way you are retrieving all records and getting a random one. If your table has too many records this could be bad for your app. – Anderson Silva Jun 14 '16 at 14:23
This one was fine for my needs since I can keep the collection and keep picking random ones (in a seeder) – Peter Mellett Jun 07 '18 at 10:35

Gras Double · Answer 3 · 2018-07-02T03:03:12.260

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 should change the 5.1 macro name to inRandomOrder so it's forward compatible ;) details, details :) — Sander Visser, Sep 16 '16 at 12:34
That's precisely one thing I did while preparing a 5.1 project before migrating it to 5.2. — Gras Double, Sep 16 '16 at 21:15

simhumileco · Answer 4 · 2019-01-08T21:01:47.080

30

You can use:

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

edited Jan 08 '19 at 21:01

answered Aug 11 '17 at 09:52

simhumileco

31,877
16
137
115

score 24 · Answer 5 · answered Aug 31 '21 at 19:19

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

Teodor Talov · Answer 6 · 2016-06-25T19:48:21.717

19

In Laravel 4 and 5 the order_by is replaced by orderBy

So, it should be:

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

edited Jun 25 '16 at 19:48

answered Nov 29 '13 at 17:43

Teodor Talov

1,933
2
25
39

User::orderBy(DB::raw('RAND()'))->get(); – Darius Dec 02 '13 at 22:16
1

It works thanks, but could you give some information how this works? – alayli Feb 16 '14 at 10:16
Can you be a little more specific? What kind of information? – Teodor Talov Feb 17 '14 at 00:33

score 11 · Answer 7 · answered Jul 14 '16 at 16:56

11

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: https://laravel.com/docs/5.2/queries#ordering-grouping-limit-and-offset

answered Jul 14 '16 at 16:56

Manuel Azar

853
2
14
24

Course::inRandomOrder()->take(20)->get(); Not working for me - bad sort specification in Find.php line 219 – Mukesh Joshi Jan 29 '19 at 07:02
2

This one's useful for model factories or db seeding – Saleh Mahmood Feb 10 '19 at 23:33

Bilal Gultekin · Answer 8 · 2013-03-23T21:20:08.307

9

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

edited Mar 23 '13 at 21:20

answered Dec 22 '12 at 23:04

Bilal Gultekin

4,831
3
22
27

3

this works as well and is a little cleaner.. ->order_by(\DB::raw('RAND()')) – Alex Naspo Jan 24 '13 at 13:34

score 4 · Answer 9 · edited Feb 03 '19 at 06:40

4

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

edited Feb 03 '19 at 06:40

simhumileco

31,877
16
137
115

answered Jul 19 '17 at 06:29

hosein azimi

321
3
5

for **ORACLE DB** ```orderByRaw('DBMS_RANDOM.RANDOM')``` – Anil Chaudhari Jun 07 '23 at 08:04

score 4 · Answer 10 · edited Dec 27 '18 at 09:29

4

Use Laravel function

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

edited Dec 27 '18 at 09:29

Eric Aya

69,473
35
181
253

answered Dec 27 '18 at 09:16

Kamlesh Paul

11,778
2
20
33

score 3 · Answer 11 · edited Feb 03 '19 at 06:38

3

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

edited Feb 03 '19 at 06:38

simhumileco

31,877
16
137
115

answered Dec 16 '14 at 09:04

ctf0

6,991
5
37
46

score 3 · Answer 12 · answered Nov 23 '19 at 14:16

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.

score 2 · Answer 13 · answered Jul 14 '15 at 01:21

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

score 2 · Answer 14 · answered Nov 15 '19 at 14:57

2

I prefer to specify first or fail:

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

answered Nov 15 '19 at 14:57

giovannipds

2,860
2
31
39

score 2 · Answer 15 · answered Apr 07 '21 at 11:46

2

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.

answered Apr 07 '21 at 11:46

hackernewbie

1,606
20
13

you are champion – Umer Fayyaz Aug 09 '23 at 16:59

score 1 · Answer 16 · edited Jun 24 '16 at 08:14

1

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

edited Jun 24 '16 at 08:14

Doc

177
1
3
14

answered Mar 13 '16 at 12:35

Krzysztof Chełchowski

119
3

The problem with this is that if there are multiple rows with ids greater than `$count` only the first of these would ever be retrieved, and so it would also be more likely to be retrieved than any other row. – kemika Sep 05 '17 at 23:27

score 0 · Answer 17 · answered Oct 24 '21 at 07:29

0

Try this code! It Works:

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

answered Oct 24 '21 at 07:29

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 24 '21 at 07:35

Laravel - Eloquent or Fluent random row

17 Answers17

Linked

Related