Results query

results_query.sql

-- Assessment - The actual test, it has_many pages
-- page - A page of questions, has_many questions
-- Question - the question the user is being asked, has_many_choices
-- choice - The choice the user selected 

-- Instance - an object that represents the users results. It has_many answers
-- answer - a join table to link the user's choice on a given question. belongs_to instance & choice
WITH
      -- Page List
      page_list AS (
        SELECT pages.id,
               pages.sort
        FROM pages
        WHERE assessment_id = assessment_id
        ORDER BY pages.sort
      ),
      -- Question List
      question_list AS (
        SELECT questions.id,
               questions.page_id,
               questions.sort
        FROM questions
        LEFT JOIN page_list page on questions.page_id=page.id
        ORDER BY page.sort, questions.sort
      ),
      -- Instance List
      instance_list AS (
        SELECT *
        FROM instances
        WHERE id IN (153,285,154,257,302,147,157,308,256,158,310,177,335,159,170,294,281,226,175,176,346,227,311,225,161,166,270,144,183,351,354,344,168,163,160,319,197,165,377,214,239,221,172,182,174,222,234,223,167,178,287,415,186,241,190,196,404,173,200,164,259,201,243,307,203,184,217,156,187,195,204,301,283,181,171,261,284,148,299,312,207,213,209,286,149,293,315,320,211,393,367,363,194,376,289,296,216,254,193,248,330,264,229,191,266,152,247,325,356,381,334,206,314,297,324,215,205,364,359,353,343,379,357,250,298,328,303,317,246,198,390,419,208,382,327,292,337,358,341,402,369,263,290,401,375,396,202,249,272,240,189,403,228,340,370,300,242,244,313,274,219,232,339,336,210,179,235,420,423,460,464,426,468,462,276,224,188,271,268,386,237,282,291,321,372,253,384,331,394,387,427,255,345,68,280,245,389,383,230,361,277,260,273,306,355,267,409,251,252,316,265,350,332,309,323,236,371,326,180,360,368,391,269,279,333,275,362,342,348,373,414,96,278,329,258,463,399,366,388,466,338,365,385,397,392,424,422,461,378,425,467,400,380,465,162,398,18,21,20,22,41,42,43,45,26,53,54,59,60,63,64,56,65,71,72,57,75,70,69,74,66,79,81,67,88,86,58,89,90,93,77,85,80,83,94,91,87,99,95,101,78,55,109,102,82,92,112,104,111,98,115,114,113,117,84,118,105,116,106,119,100,121,122,123,108,107,129,126,128,110,127,125,131,130,132,134,133,135,138,31,120,137,140,139,136,141,143,142)
      ),
      -- Answer List
      answer_list AS (
        SELECT answers.id,
               answers.instance_id,
               answers.choice_id
        FROM answers
        WHERE instance_id IN (153,285,154,257,302,147,157,308,256,158,310,177,335,159,170,294,281,226,175,176,346,227,311,225,161,166,270,144,183,351,354,344,168,163,160,319,197,165,377,214,239,221,172,182,174,222,234,223,167,178,287,415,186,241,190,196,404,173,200,164,259,201,243,307,203,184,217,156,187,195,204,301,283,181,171,261,284,148,299,312,207,213,209,286,149,293,315,320,211,393,367,363,194,376,289,296,216,254,193,248,330,264,229,191,266,152,247,325,356,381,334,206,314,297,324,215,205,364,359,353,343,379,357,250,298,328,303,317,246,198,390,419,208,382,327,292,337,358,341,402,369,263,290,401,375,396,202,249,272,240,189,403,228,340,370,300,242,244,313,274,219,232,339,336,210,179,235,420,423,460,464,426,468,462,276,224,188,271,268,386,237,282,291,321,372,253,384,331,394,387,427,255,345,68,280,245,389,383,230,361,277,260,273,306,355,267,409,251,252,316,265,350,332,309,323,236,371,326,180,360,368,391,269,279,333,275,362,342,348,373,414,96,278,329,258,463,399,366,388,466,338,365,385,397,392,424,422,461,378,425,467,400,380,465,162,398,18,21,20,22,41,42,43,45,26,53,54,59,60,63,64,56,65,71,72,57,75,70,69,74,66,79,81,67,88,86,58,89,90,93,77,85,80,83,94,91,87,99,95,101,78,55,109,102,82,92,112,104,111,98,115,114,113,117,84,118,105,116,106,119,100,121,122,123,108,107,129,126,128,110,127,125,131,130,132,134,133,135,138,31,120,137,140,139,136,141,143,142)
      ),
      -- User List
      user_list AS (
        SELECT users. id,
               users.name
        FROM users
        LEFT JOIN instance_list on users.id=instance_list.user_id
      ),
      -- Choice list
      choice_list as (
        SELECT instance_list.id as instance_id,
               choice.id as choice_id,
               choice.admin_value as question_5, choice.admin_value as question_33, choice.admin_value as question_69, choice.admin_value as question_84, choice.admin_value as question_9, choice.admin_value as question_64, choice.admin_value as question_70, choice.admin_value as question_85, choice.admin_value as question_10, choice.admin_value as question_11, choice.admin_value as question_12, choice.admin_value as question_13, choice.admin_value as question_30, choice.admin_value as question_31, choice.admin_value as question_8, choice.admin_value as question_37, choice.admin_value as question_36, choice.admin_value as question_67, choice.admin_value as question_66, choice.admin_value as question_72, choice.admin_value as question_73, choice.admin_value as question_77, choice.admin_value as question_79, choice.admin_value as question_78, choice.admin_value as question_75, choice.admin_value as question_76, choice.admin_value as question_81, choice.admin_value as question_82, choice.admin_value as question_88, choice.admin_value as question_87, choice.admin_value as question_90, choice.admin_value as question_91, choice.admin_value as question_94, choice.admin_value as question_93, choice.admin_value as question_96, choice.admin_value as question_97, choice.admin_value as question_99, choice.admin_value as question_100, choice.admin_value as question_105, choice.admin_value as question_102, choice.admin_value as question_106, choice.admin_value as question_104, choice.admin_value as question_103, choice.admin_value as question_108, choice.admin_value as question_109, choice.admin_value as question_111, choice.admin_value as question_112, choice.admin_value as question_115, choice.admin_value as question_114, choice.admin_value as question_118, choice.admin_value as question_117, choice.admin_value as question_121, choice.admin_value as question_120, choice.admin_value as question_125, choice.admin_value as question_126, choice.admin_value as question_124, choice.admin_value as question_123, choice.admin_value as question_127, choice.admin_value as question_130, choice.admin_value as question_129, choice.admin_value as question_133, choice.admin_value as question_132, choice.admin_value as question_140, choice.admin_value as question_139, choice.admin_value as question_137, choice.admin_value as question_141, choice.admin_value as question_135, choice.admin_value as question_138, choice.admin_value as question_136, choice.admin_value as question_144, choice.admin_value as question_143, choice.admin_value as question_147, choice.admin_value as question_146, choice.admin_value as question_150, choice.admin_value as question_149, choice.admin_value as question_152, choice.admin_value as question_157, choice.admin_value as question_158, choice.admin_value as question_154, choice.admin_value as question_155, choice.admin_value as question_156, choice.admin_value as question_153, choice.admin_value as question_161, choice.admin_value as question_160, choice.admin_value as question_163, choice.admin_value as question_164, choice.admin_value as question_166, choice.admin_value as question_167, choice.admin_value as question_170, choice.admin_value as question_169, choice.admin_value as question_172, choice.admin_value as question_173, choice.admin_value as question_176, choice.admin_value as question_175, choice.admin_value as question_179, choice.admin_value as question_178, choice.admin_value as question_184, choice.admin_value as question_185, choice.admin_value as question_181, choice.admin_value as question_187, choice.admin_value as question_186, choice.admin_value as question_183, choice.admin_value as question_182, choice.admin_value as question_189, choice.admin_value as question_190, choice.admin_value as question_193, choice.admin_value as question_192, choice.admin_value as question_195, choice.admin_value as question_196, choice.admin_value as question_198, choice.admin_value as question_199, choice.admin_value as question_201, choice.admin_value as question_202, choice.admin_value as question_204, choice.admin_value as question_205, choice.admin_value as question_207, choice.admin_value as question_211, choice.admin_value as question_208, choice.admin_value as question_209, choice.admin_value as question_210, choice.admin_value as question_214, choice.admin_value as question_213, choice.admin_value as question_216, choice.admin_value as question_217, choice.admin_value as question_220, choice.admin_value as question_219, choice.admin_value as question_223, choice.admin_value as question_222, choice.admin_value as question_225, choice.admin_value as question_226, choice.admin_value as question_229, choice.admin_value as question_228, choice.admin_value as question_231, choice.admin_value as question_232, choice.admin_value as question_235, choice.admin_value as question_234, choice.admin_value as question_237, choice.admin_value as question_238, choice.admin_value as question_240, choice.admin_value as question_241, choice.admin_value as question_244, choice.admin_value as question_243, choice.admin_value as question_246, choice.admin_value as question_247, choice.admin_value as question_249, choice.admin_value as question_250, choice.admin_value as question_253, choice.admin_value as question_252, choice.admin_value as question_256, choice.admin_value as question_255, choice.admin_value as question_258, choice.admin_value as question_259, choice.admin_value as question_261, choice.admin_value as question_262, choice.admin_value as question_265, choice.admin_value as question_264, choice.admin_value as question_268, choice.admin_value as question_267, choice.admin_value as question_271, choice.admin_value as question_270, choice.admin_value as question_273, choice.admin_value as question_274, choice.admin_value as question_278, choice.admin_value as question_276, choice.admin_value as question_277, choice.admin_value as question_281, choice.admin_value as question_280, choice.admin_value as question_283, choice.admin_value as question_284, choice.admin_value as question_287, choice.admin_value as question_286, choice.admin_value as question_290, choice.admin_value as question_289, choice.admin_value as question_292, choice.admin_value as question_293, choice.admin_value as question_295, choice.admin_value as question_296, choice.admin_value as question_299, choice.admin_value as question_298, choice.admin_value as question_301, choice.admin_value as question_302, choice.admin_value as question_304, choice.admin_value as question_305, choice.admin_value as question_307, choice.admin_value as question_308, choice.admin_value as question_310, choice.admin_value as question_311, choice.admin_value as question_314, choice.admin_value as question_313, choice.admin_value as question_316, choice.admin_value as question_317, choice.admin_value as question_319, choice.admin_value as question_320, choice.admin_value as question_329, choice.admin_value as question_330, choice.admin_value as question_331
        FROM choices choice
        LEFT JOIN question_list ON question_list.id=choice.question_id
        LEFT JOIN page_list ON page_list.id=question_list.page_id
        LEFT JOIN answer_list ON answer_list.choice_id= choice.id
        LEFT JOIN instance_list ON instance_list.user_id=answer_list.instance_id
        ORDER BY page_list.sort, question_list.sort
      )
      -- Group all queries on one query joined on instance
      SELECT instance.id,
             user_list.name,
             choice.*
      FROM instance_list instance
      LEFT JOIN user_list ON instance.user_id = user_list.id
      LEFT JOIN choice_list choice ON instance.id = choice.instance_id