LearningROR LearningROR - 1 month ago 8
MySQL Question

How to group the records based to 3rd table belongs to, has many

I have below code:

quiz.rb

has_many :quiz_attempts


student.rb

has_many :quiz_attempts


quiz_attempt.rb

belongs_to :student
belongs_to :quiz


What I need to do is to get all records of student from quiz_attempts table which has quiz id and show the records of quiz table based on it.

Means, if students has taken one quiz A 10 times and quiz B 5 times, so I need to show all these rows as 2 from quiz table.

In view I will have only 2 records of quiz table which are associated with 15 records of A and B collectively in quiz_attempts table.

quiz_attempts has
quiz_id
column and student_id column as well, quiz table has no student id in it.

The query I am working with is:

@quizzes = @student.quiz_attempts.includes(:quiz)


but the problem is that, it still shows me 2 rows but from quiz_attempts table and I cant group them as quiz table.

I need to have complete solution.

Answer
Quiz.joins(quiz_attempts: :student)
    .where.not(students: { id: nil })
    .group('quizzez.id')

What above does is basically the following:

  1. Quiz.joins(quiz_attempts: :student) - select all rows from quizzes table, which have connection with quiz_attempts and students tables.
  2. .where.not(students: { id: nil }) from above select only those quizzes, which have students who has taken quiz_attempts.
  3. .group('quizzez.id') says that we do not want to have the same quiz shown multiple times because lots of students have attempted to pass it, but only uniq records.

EDIT

As discussed in comments, to have above query be based on current_user's id, you could implement a AR scope:

class Quiz < ActiveRecord::Base
  scope :by_student_id, lambda { |student_id|
    joins(quiz_attempts: :student).where(students: { id: student_id }).group('quizzez.id')
  }
end

To use this scope you'd write:

Quiz.by_student_id(current_user.id)
Comments