Rails ActiveQueryInterface

Question

I am facing a problem while I try to write a rails query using joins.

What I am building is a hash having candidate_answers grouped according to sections of a given question paper. (N.B. I have a pool of questions from where some are added in different sections of a questiion paper)

I have achieved my solution using map as:

exam_candidate.exam.question_paper.sections.includes(:questions).each do |section|
  if section.questions.present?
    section_question_hash[section] = candidate_answers.where(question_id: section.questions.map(&:id))
  end
end

Since using the above creates a lot of database queries running on the background, it is not healthy to use, and thus I need to use joins. Also, I am able to write a SQL query for the same as

select b.name, group_concat(c.id) from sections b
left join question_papers_questions a on a.section_id = b.id 
left join candidate_answers c on a.question_id = c.question_id 
where a.question_paper_id = 3 and c.exam_candidate_id = 4 
group by (b.name)

But while I try the same in rails I am having lots of issues with it.

Here's my model structure:

class ExamCandidate < ActiveRecord::Base
  belongs_to :exam
  belongs_to :candidate
  has_many :candidate_answers, dependent: :delete_all
  accepts_nested_attributes_for :candidate_answers
end

class Exam < ActiveRecord::Base
  has_many :exam_candidates, dependent: :destroy
  has_many :candidates, through: :exam_candidates
  belongs_to :question_paper
end

class QuestionPaper < ActiveRecord::Base
  has_many :exams, dependent: :nullify
  has_many :exam_candidates, through: :exams
  has_many :questions, through: :question_papers_questions
  has_many :question_papers_questions
  has_many :sections, dependent: :destroy
end

class QuestionPapersQuestion < ActiveRecord::Base
  belongs_to :question
  belongs_to :question_paper
  belongs_to :section
end

class Question < ActiveRecord::Base
  has_many :candidate_answers, through: :answers
  has_many :exams, through: :question_papers
  has_many :exam_candidates, through: :exams
  has_many :question_papers_questions
  has_many :question_papers, through: :question_papers_questions
end

class Section < ActiveRecord::Base
  belongs_to :question_paper
  has_many :questions, through: :question_papers_questions
  has_many :question_papers_questions
end

class CandidateAnswer < ActiveRecord::Base
  belongs_to :exam_candidate
  belongs_to :question
end

I have given enough time on it, but being almost a newbie in rails is my disadvantage, if anyone can try it or suggest something it would be very helpful.

Can you explain the issues you're having with the query? Do you need it converted to an activerecord query or can you just use the raw SQL? — NiftyAsp, May 10 '18 at 14:19
Here is someone with a similar problem https://stackoverflow.com/questions/28146848/how-can-i-use-group-concat-in-rails — rails_newbie, May 11 '18 at 05:36

score 1 · Accepted Answer · answered May 17 '18 at 10:23

sections = exam.question_paper.sections.select("sections.name, group_concat(candidate_answers.id) as candidate_answer_ids")
  .joins(:question_papers_questions).joins("inner join candidate_answers on question_papers_questions.question_id = candidate_answers.question_id")
  .where(candidate_answers: {exam_candidate_id: id}, question_papers_questions: { question_paper_id: exam.question_paper_id })
  .group("sections.name")

Rails ActiveQueryInterface

1 Answers1