Take 10 random rows from the top 100

Question

Using Laravel Eloquent, how can I take 10 random rows from the top 100 (sorted by date).

For example, I have this:

$comments = Comment::orderBy('created_at', 'DESC')
    ->take(100)
    ->inRandomOrder()
    ->get();

How can I change this so that it takes 10 random rows from the 100 selected? Is there a way to do this such that I don't have to retrieve 100 rows?

Answer 1

1 - inRandomOrder() is really slow, so I wouldn't recommend you to use it.

2 - take(100)->random() solution will always get 100 rows into the memory and only then will get 10 random rows. You mentioned you're getting an error when there are only 5 items in the colleciton, so use this code instead:

$comments = Comment::latest()->take(100)->get();
$count = $comments->count() > 9 ? 10 : $comments->count();
$random =  $comments->random($count);

3 - If this query is being executed really often in you app, I would recommend you to create additional column sort and update this column with scheduled task. Set 1 to 100 integers as values of the sort column every day or few hours. Apply these numbers to the 100 latest rows in the table, other rows set to 0.

Then you'll be able to get latest radnom 10 rows with fast query:

$comments = Comment::orderBy('sort')->take(10)->get();

It looks like complicated solution, but on high load system it's one of the best options.

Answer 2

You can use random():

  $comments = Comment::orderBy('created_at', 'DESC')
        ->take(100)
        ->get()
        ->random(10);