Skip to content

Instantly share code, notes, and snippets.

@jonathansimmons
Created December 17, 2016 16:25
Show Gist options
  • Save jonathansimmons/f3d7a464f41d60a075531d5af2616c71 to your computer and use it in GitHub Desktop.
Save jonathansimmons/f3d7a464f41d60a075531d5af2616c71 to your computer and use it in GitHub Desktop.
Results query
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment