Question 16370 in Laravel Question posted in
The official Laravel Documentation can be found here.

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.

Tags:

3

Answers


  1. 0

    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());
}
    Login or Signup to reply.
  2. 0

    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();
    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search