Rspec - Should we use fixtures to test a database query?

Question

Using ruby(1.8.7), rails(2.3.8), rspec(1.3.1) and rspec-rails(1.3.3) how do I test a method that is full of complex SQL queries? Is there a way to make use of stubs and mocks. For example, a method like:

class Bucket << ActiveRecord::Base
  attr_accessor :students
  def populate_students_subassesmentwise(subassesment, mentor)
    student_ids = Mark.find(:all,
      :joins => "#{self.student_current_klass} #{self.current_mentors}",
      :conditions => ["marks.subject_id = ? AND st.klass_id = ? AND 
                   IF(sub.is_elective IS TRUE, 
                     (se.student_id = st.id AND se.subject_klass_id = marks.klass_id AND se.student_klass_id = st.klass_id AND marks.subject_id = se.subject_id),
                     (marks.klass_id = st.klass_id))
                   AND u.account_enabled IS TRUE AND sub.active IS TRUE AND k.active IS TRUE AND marks.markable_id = ? AND marks.markable_type = ?
                   AND ROUND(marks_obtained/marks_total*100) BETWEEN ? AND ? ",
                   mentor.subject_id, mentor.klass_id, subassesment.id, "Subassesment", min_range, max_range],
      :group => "marks.id").collect(&:student_id)

    self.assign_students(student_ids) # This is a call to another instance method that runs a query to find the students having ids equal to student ids and assign it to the students attr_accessor
  end
end

As you can see there are a lot of JOIN, IF and WHERE clauses, that are more of talking in 'DBMS' terms, how do I write tests in Rspec that would mock this query? Or should we use fixtures?

Answer 1

Personally I would test this using fixtures (or rather, FactoryGirl). Since you're really testing your sql/database code it seems silly to me not to test this in the database. Unless you have a seperate sql unittesting setup or something (which seems like overkill to me ;) (in most scenarios))