2

I have these model:

class Question
  has_many :answers
end

class Answer
  belongs_to :question
end

class Exam
  belongs_to :general_exam
  belongs_to :user
  has_many :questions, through: :exam_questions
end

class ExamQuestion
  belongs_to :exam
  belongs_to :question
end

Currently, I want to get all questions in Exam and answers of questions, so I used Specifying Conditions on Eager Loaded Associations, I ran this in console:

exam = Exam.find(16)
questions = Question.includes(:answers).where("id = ?", exam.question_ids)

Output in console after run questions = ...:

SELECT "questions".id FROM "questions" INNER JOIN "exam_questions" ON "questions"."id" = "exam_questions"."question_id" WHERE "exam_questions"."exam_id" = 16 ORDER BY questions.created_at DESC
  Question Load (0.8ms)  SELECT "questions".* FROM "questions" WHERE (id = 170,162,160) ORDER BY questions.created_at DESC
=> #<ActiveRecord::Relation:0x4c07ebc>

The first strange thing is, I saw in query, it did a INNER JOIN, but in rails guide, it said query will make a LEFT OUTER JOIN, I don't know why this is different.

Second thing, now I want to get question objects in questions, I ran:

questions.each do |q|
  puts q.content
end

It returned error:

SELECT "questions".* FROM "questions" WHERE (id = 170,162,160) ORDER BY questions.created_at DESC
ActiveRecord::StatementInvalid: PG::Error: ERROR:  argument of WHERE must be type boolean, not type record
LINE 1: SELECT "questions".* FROM "questions"  WHERE (id = 170,162,1...

How can I get question objects now?

Thanh
  • 8,219
  • 5
  • 33
  • 56

1 Answers1

2

It looks like your where clause is wrong. Try:

where(:id => exam.question_ids)

When you provide the string version, "id = ?", the database adapters won't convert that to an in-clause. When you provide the hash version, the database adapters will recognize that the value is an array and use an in.

Perhaps a more efficient way would be to approach the problem differently:

class Question
  has_many :exam_questions
end

questions = Question.joins(:exam_questions).where(:exam_questions => {:exam_id => 16})

See Efficient ActiveRecord has_and_belongs_to_many query

Community
  • 1
  • 1
John Naegle
  • 8,077
  • 3
  • 38
  • 47
  • I also think that the part of the condition that is `exam.question_ids` may itself result in a query, which may mitigate the performance benefit of eager loading the question answers. – Tom Harrison Nov 29 '12 at 16:12
  • 2
    Yes, especially if that query gets turned into active record objects. You could use pluck to just grab the ids: where(:id => exam.questions.pluck(:id)). There are other ways to approach this as well. – John Naegle Nov 29 '12 at 16:16
  • Yeah, it worked. Your `questions = Question.joins(:exam_questions).where(:exam_questions => {:exam_id => 16}) ` didn't work, it returned an `# `, like the where clause in my question. @tharrison, @John, so is my query good? what do you mean mitigate the performance benefit of eager loading? – Thanh Nov 29 '12 at 16:33
  • 1
    The relation is lazy evaluated. You can manipulate it further (questions.where(...)) or use it in an evaluation context (right term?): questions.all or questions.each do { |q| ... }. Both of those will evaluate the query and create the ActiveRecord objects you are expecting. RE: mitigation of eager loading performance. Look at the queries that get run when you use exam.question_ids: it will load all the questions, then grab the ID column - its not very efficient. – John Naegle Nov 29 '12 at 16:42
  • 1
    Think of `ActiveRecord::Relation` as a query that's all ready, and just needs to be fired off. Add `.all` if you expect a collection or `.first` if you expect a single record in order to query. I do think you'll find the link @JohnNaegle added at the bottom of his answer helpful in understanding. In short, if you are going to *use* all of the data you get back (e.g. display, compare, etc.) then it's ideal to have it all returned in as few SQL queries as possible -- one, or two is good. Where things slow down is when you have N+1 queries -- your log will tell you what's happening. – Tom Harrison Nov 29 '12 at 16:47