Make tests pass on MySQL

MySQL did not like some of our hand written SQL. Rewrote the parts
so that tests pass on both PostgreSQL and MySQL. Changes in
multiple choice setup in features are because the messy way we do
things didn't work on MySQL. A cleanup of the steps is called for later.

5 files changed, 16 insertions, 14 deletions

diff --git a/app/models/answer.rb b/app/models/answer.rb
index 4410980..ff45ee5 100644
--- a/app/models/answer.rb
+++ b/app/models/answer.rb
@@ -93,8 +93,8 @@ class Answer < ActiveRecord::Base
 
   def self.wrong_answers_of(uid)
     Answer.find_by_sql ["SELECT ans.* FROM answers ans, answers ref WHERE
-      ref.reference = 't' AND ans.question_id = ref.question_id AND
-      ans.content != ref.content AND ans.owner_id = ?", uid]
+      ref.reference = ? AND ans.question_id = ref.question_id AND
+      ans.content != ref.content AND ans.owner_id = ?", true, uid]
   end
 
   protected
diff --git a/app/models/question.rb b/app/models/question.rb
index dfe7b77..e3fdd08 100644
--- a/app/models/question.rb
+++ b/app/models/question.rb
@@ -108,7 +108,7 @@ class Question < ActiveRecord::Base
   named_scope   :with_content, :include => [:question_content_text,
     :question_content_multiple_choice, :question_content_email], :conditions =>
     'question_content_texts.id IS NOT NULL OR question_content_multiple_choices.id
-    IS NOT NULL OR question_content_emails IS NOT NULL'
+    IS NOT NULL OR question_content_emails.id IS NOT NULL'
 
   named_scope :questions_to_approve, :conditions => { :approved => false }
 
diff --git a/app/models/user.rb b/app/models/user.rb
index a974abb..e289255 100644
--- a/app/models/user.rb
+++ b/app/models/user.rb
@@ -29,13 +29,13 @@ class User < ActiveRecord::Base
   named_scope :recruits_answered_all, :conditions => "role = 'recruit' AND NOT EXISTS
     (SELECT questions.id FROM questions
     INNER JOIN question_categories cat ON questions.question_category_id = cat.id INNER JOIN
-    user_categories ON user_categories.question_category_id = cat.id LEFT OUTER JOIN
-    answers ON answers.question_id = questions.id AND answers.owner_id = users.id WHERE
-    user_categories.user_id = users.id AND answers.id IS NULL AND questions.question_group_id IS NULL)
+    user_categories ON user_categories.question_category_id = cat.id  WHERE
+    user_categories.user_id = users.id AND questions.question_group_id IS NULL AND NOT EXISTS (
+    SELECT answers.id FROM answers WHERE answers.question_id = questions.id AND answers.owner_id = users.id))
     AND NOT EXISTS
     (SELECT questions.id FROM questions INNER JOIN user_question_groups ON questions.id = user_question_groups.question_id
-    LEFT OUTER JOIN answers ON answers.question_id = questions.id AND answers.owner_id = users.id
-     WHERE user_question_groups.user_id = users.id AND answers.id IS NULL)"
+     WHERE user_question_groups.user_id = users.id AND NOT EXISTS (
+     SELECT answers.id FROM answers WHERE answers.question_id = questions.id AND answers.owner_id = users.id))"
 
   # --- Signup lifecycle --- #
   lifecycle do
diff --git a/features/reference_answers.feature b/features/reference_answers.feature
index 944a196..4a6dd6f 100644
--- a/features/reference_answers.feature
+++ b/features/reference_answers.feature
@@ -25,7 +25,7 @@ Feature: Reference answers
 
   Scenario: Record reference answer for multiple choice question and edit it as different recruiter
     Given I am logged in as "recruiter1" who is "recruiter"
-    And a multiple choice content "Chose some" for "question"
+    And a multiple choice content "question" for "question"
     And following options for "question":
       |Opt 1|Opt 2|Opt 3|
     When I am on show "question" question page
diff --git a/features/step_definitions/multiple_choice_question_steps.rb b/features/step_definitions/multiple_choice_question_steps.rb
index 242e46f..e44d7f2 100644
--- a/features/step_definitions/multiple_choice_question_steps.rb
+++ b/features/step_definitions/multiple_choice_question_steps.rb
@@ -6,12 +6,14 @@ Given /^a multiple choice content "([^\"]*)"$/ do |content|
 end
 
 Given /^a multiple choice content "([^\"]*)" for "([^\"]*)"$/ do |content, question|
-  Given "a multiple choice content \"#{content}\""
   Given "a question \"#{question}\""
-  @question.content.destroy unless @question.content.is_a?(QuestionContentMultipleChoice) || @question.content.nil?
-  @question.reload
-  @content.question = @question
-  @content.save!
+  unless @question.content.is_a?(QuestionContentMultipleChoice) || @question.content.nil?
+    @question.content.destroy
+    Given "a multiple choice content \"#{content}\""
+    @content.question = @question
+    @content.save!
+    @question.reload
+  end
 end
 
 Given /^(?:|an )option "([^\"]*)" for "([^\"]*)"$/ do |opt, content|