Constructing queries for collecting survey data from my survey database design

Question

I have a relatively simple model design for creating surveys, assigning surveys to users and filling out surveys by those users. It all works fine, but when it comes to retrieving data from those surveys I am in a state where I am not sure what is the right approach, especially what is the rails way.

My model design is as follows: (for clarity left only the relevant code)

class Survey < ActiveRecord::Base
  has_many :survey_sections, dependent: :destroy

  has_many :survey_assignments
  has_many :participants , :through => :survey_assignments, :source => :user

  has_many :survey_responses
end

class SurveySection < ActiveRecord::Base
  belongs_to :survey
  has_many :survey_questions, dependent: :destroy
  has_many :survey_options, dependent: :destroy
end

So surveys are divided into many sections and each section has many questions, also each section has many options, options are basically a predefined answers for all the questions in the current section. This is design decision, so I don't need each question to have many options...

class SurveyQuestion < ActiveRecord::Base
  belongs_to :survey_section
  has_many :survey_answers
end

class SurveyAnswer < ActiveRecord::Base
  belongs_to :survey_option
  belongs_to :survey_question
  belongs_to :survey_response
end

class SurveyOption < ActiveRecord::Base
  #it has 2 fields 'weight'(int) and 'text'(string)
  belongs_to :survey_section
end

Doctors can assign surveys to their patients and specify the date until when can they take the survey.

class SurveyAssignment < ActiveRecord::Base
  #has also a field valid_until(date)  
  belongs_to :user
  belongs_to :survey

  has_many :survey_responses
end

I am also logging a survey response. So everytime a user filled out the form a new record in survey_responses table is created. I will need this later, because of the scheduling implementation, where user could possibly have surveys assigned that he should be taking [enter some number here] X [day|week|month].

class SurveyResponse < ActiveRecord::Base
  belongs_to :survey_assignment
  belongs_to :survey
  belongs_to :user

  has_many :survey_answers
 end

Now I need to display a graph of survey results. Like you see in the code, results are integers (see SurveyOption class) that I need to add up for particular survey that particular user took. So lets say I have a user_id and a survey_id, now I want to retrieve all of that users survey responses, but for each survey response I need the sum of weights(SurveyOption) that their answers have.

If I do a following query, I get back the sum of his answers for all his responses:

SurveyResponse.joins(:survey_answers => :survey_option).where(user_id: 96, survey_id:63).select("survey_option.weight").sum("weight")

But I would like to get back for every response of that user for that survey an array, something like this

[[survey_response.created_at.to_i, the_sum_of_weights],[...],[...],....]

So what is the best way of doing something like this? Is it possible to do it in a single query, if I think purely in sql statemets I think this could be done in a single query.

If someone needs me to elaborate further, we can discuss in the comments. Thanks for helping me out.

I guess the sql tatement I'm looking to produce is :

SELECT survey_responses.id, survey_responses.created_at, SUM (survey_options.weight) FROM "survey_responses" INNER JOIN "survey_answers" ON "survey_answers"."survey_response_id" = "survey_responses"."id" INNER JOIN "survey_options" ON "survey_options"."id" = "survey_answers"."survey_option_id" WHERE "survey_responses"."user_id" = 96 AND "survey_responses"."survey_id" = 64  GROUP BY survey_responses.id, survey_responses.created_at;

I think I'm getting close to solving my own question :)))

Answer 1

So after a bit of research I found one way to do it.

result = SurveyResponse.joins(:survey_answers => :survey_option).where(user_id: 96, survey_id: 64).select("SUM (survey_options.weight) as score, survey_responses.created_at").group("survey_responses.created_at, survey_responses.id")

Then I could use the find_each function on ActiveRecord::Relation object.

result.find_each do |r|
  puts " score: #{r.score} created_at: #{r.created_at}"
end

find_each is better than each, because it loads a batch of 1000 records at a time, so it doesn't hog up your memmory. Imagine loading millions of records.

If anyone has a better solution for querying, please share and I will accept it.