4

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?

  • using `inRandomOrder()` will use mysql `Rand()` function which might be slow for huge data. So better use `random()` of collection function which will random the collection data / array after finished fetched first 100. Check further discussion here http://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast – xmhafiz Nov 24 '16 at 04:11

2 Answers2

4

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.

Alexey Mezenin
  • 158,981
  • 26
  • 290
  • 279
  • can you explain this line `$count = $comments->count() > 9 ? 10 : $comments->count();` please. – Sanzeeb Aryal Jan 28 '17 at 13:19
  • @SanzeebAryal this code sets a number of random items to get. If there are just 3 items in a table, this code set limit to 3. This will help to avoid errors. – Alexey Mezenin Jan 28 '17 at 14:01
  • and how do i read this code. something like if `$comments->count()` is greater than 9. take 10, else take all? or can you suggest me a link for this particular kind of code? thank you. – Sanzeeb Aryal Jan 28 '17 at 14:07
  • @SanzeebAryal this is the ternary operator. Read [this article](https://www.abeautifulsite.net/how-to-use-the-php-ternary-operator), you'll find a really cool code example which will help you to understand how this operator works. – Alexey Mezenin Jan 28 '17 at 14:11
0

You can use random(): 

  $comments = Comment::orderBy('created_at', 'DESC')
        ->take(100)
        ->get()
        ->random(10);
Sanzeeb Aryal
  • 4,358
  • 3
  • 20
  • 43
  • If there are less than 10 results to get (i.e. if the `take(100)` only found, for example, 5 results), it gives me the error `You requested 10 items, but there are only 5 items in the collection`. –  Nov 24 '16 at 04:35
  • @user7203124 it's not a bug, it's a feature, as a L's devteam member told me 2yrs back. – Peter Nov 24 '16 at 07:17