LearningROR LearningROR - 1 year ago 72
MySQL Question

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

I have below code:


has_many :quiz_attempts


has_many :quiz_attempts


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
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 Source
Quiz.joins(quiz_attempts: :student)
    .where.not(students: { id: nil })

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.


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')

To use this scope you'd write:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download