Databases
StackOverflow2013
Postsdbo
POConstructing queries for collecting survey data from my survey database design
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POConstructing queries for collecting survey data from my survey database design
    primarykey
    data
    text
    <p>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. </p> <p>My model design is as follows: (for clarity left only the relevant code)</p> <pre><code>class Survey &lt; ActiveRecord::Base has_many :survey_sections, dependent: :destroy has_many :survey_assignments has_many :participants , :through =&gt; :survey_assignments, :source =&gt; :user has_many :survey_responses end class SurveySection &lt; ActiveRecord::Base belongs_to :survey has_many :survey_questions, dependent: :destroy has_many :survey_options, dependent: :destroy end </code></pre> <p>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...</p> <pre><code>class SurveyQuestion &lt; ActiveRecord::Base belongs_to :survey_section has_many :survey_answers end class SurveyAnswer &lt; ActiveRecord::Base belongs_to :survey_option belongs_to :survey_question belongs_to :survey_response end class SurveyOption &lt; ActiveRecord::Base #it has 2 fields 'weight'(int) and 'text'(string) belongs_to :survey_section end </code></pre> <p>Doctors can assign surveys to their patients and specify the date until when can they take the survey. </p> <pre><code>class SurveyAssignment &lt; ActiveRecord::Base #has also a field valid_until(date) belongs_to :user belongs_to :survey has_many :survey_responses end </code></pre> <p>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].</p> <pre><code>class SurveyResponse &lt; ActiveRecord::Base belongs_to :survey_assignment belongs_to :survey belongs_to :user has_many :survey_answers end </code></pre> <p>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. </p> <p>If I do a following query, I get back the sum of his answers for all his responses:</p> <pre><code>SurveyResponse.joins(:survey_answers =&gt; :survey_option).where(user_id: 96, survey_id:63).select("survey_option.weight").sum("weight") </code></pre> <p>But I would like to get back for every response of that user for that survey an array, something like this</p> <pre><code>[[survey_response.created_at.to_i, the_sum_of_weights],[...],[...],....] </code></pre> <p>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.</p> <p>If someone needs me to elaborate further, we can discuss in the comments. Thanks for helping me out. </p> <p>I guess the sql tatement I'm looking to produce is :</p> <pre><code>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; </code></pre> <p>I think I'm getting close to solving my own question :)))</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USblushrt
    1. USblushrt
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload