menu

Questions & Answers

Laravel Eloquent : Select random rows on relation based

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.

Comments:
2023-01-11 09:00:17
You just asked this question: stackoverflow.com/questions/75007974/…. Why are you asking it again?
2023-01-11 09:00:17
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.
2023-01-11 09:00:17
I just deleted it since I was not clear in there.
2023-01-11 09:00:17
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.
2023-01-11 09:00:17
have you tried group by category_id
2023-01-11 09:00:17
No I have not tried that
Answers(1) :

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