Laravel Eloquent : Select random rows on relation based

Question

I want to pick questions randomly but category dependent. For example, if the test is given with 10 questions and the total category is 5, the test flow should take 2 questions randomly from each category. Is there a way to select it through random and eloquent relations?

and the question table

+-------+-------+-------+-------+
|  id   |  category_id  |.......|
+-------+-------+-------+-------+

already I am using random eloquent but the probability of getting questions from each category is low

public getRandomQuestions($limit)
{
    $this->inRandomOrder()->limit($limit)->get()
}

and I'm clueless when it's coming to relations.

You just asked this question: https://stackoverflow.com/questions/75007974/laravel-eloquent-take-random-questions-category-based. Why are you asking it again? — Tim Lewis, Jan 04 '23 at 16:06
Sidenote, deleting and reposting the question because it had downvotes is not the way to get answers on Stackoverflow. It's a great way to get banned from asking questions though. — Tim Lewis, Jan 04 '23 at 16:08
Right, but you posted it again without changing anything... Why? There's an "Edit" button below your question if you need to change anything (which you did). Don't misuse the system, or you won't be able to use the system at all. — Tim Lewis, Jan 04 '23 at 16:15
Could you please clarify what you mean by " the probability of getting questions from each category is low" ? Do you mean that you are getting results only some of the time that add up from 0 to the `$limit` ? Is every category currently populated with at least `$limit` elements ? — Louis-Eric Simard, Jan 15 '23 at 02:46

score 4 · Answer 1 · answered Jan 14 '23 at 19:42

You can also use the inRandomOrder and groupBy method together to select random questions from each category.

$questions = Question::with('category')->inRandomOrder()->groupBy('category_id')->limit(2)->get();

This will give you 2 random questions from each category.

You can also use subquery to select questions with certain number of random questions per category

$questions = Question::with('category')
                      ->whereIn('category_id', function($query) use ($limit) {
                            $query->select('category_id')
                                  ->from('questions')
                                  ->groupBy('category_id')
                                  ->inRandomOrder()
                                  ->limit($limit)
                      })->get();

score 1 · Answer 2 · answered Jan 11 '23 at 08:11

the query to get 1 random question for each category:

SELECT *
FROM
  (SELECT *, 
          @position := IF(@current_cate=category_id, @position + 1, 1) AS POSITION, 
          @current_cate := category_id
   FROM
     (SELECT q.*
      FROM category c
      INNER JOIN question q ON c.id = q.category_id
      ORDER BY RAND()) temp
   ORDER BY category_id) temp1
WHERE POSITION <= 2
ORDER BY category_id;

explanation:

since you want the question to be take randomly we need order by rand(), note: inRandomOrder also uses order by rand() under the hood
to be able to get 2 questions for each category, we need a variable (@position) to mark the order of question

laravel implementation:

public getRandomQuestions($limit)
{
    $questions = DB::select("SELECT *
    FROM
      (SELECT *, 
              @position := IF(@current_cate=category_id, @position + 1, 1) AS POSITION, 
              @current_cate := category_id
       FROM
         (SELECT q.*
          FROM category c
          INNER JOIN question q ON c.id = q.category_id
          ORDER BY RAND()) temp
       ORDER BY category_id) temp1
    WHERE POSITION <= 2
    ORDER BY category_id");

    return Question::hydrate($questions->toArray());
}

Is there a way to use Eloquent instead of raw query, Chinh Ngugen? — OutForCode, Jan 12 '23 at 06:48

score 1 · Answer 3 · answered Jan 16 '23 at 06:58

If you're using PHP >= 7.2 the use shuffle()

public function getRandomQuestions($limit) {
    return Question::limit($limit)->groupBy('category_id')->get()->shuffle();
}

or else

public function getRandomQuestions($limit) {
    return Question::inRandomOrder()->limit($limit)->groupBy('category_id')->get();
}

the trick is you need to use groupBy() clause for this

Laravel Eloquent : Select random rows on relation based

3 Answers3