LimeSurvey queries
Background
For a couple of projects that we did on civil participation, we needed to be able to hold a survey. So, I decided to see which open-source packages were available to do this. Our goal was to gather the data and inject it into DokuWiki to analyze and re-use the answers given by participants.
Below, you'll see one of my first successful attempts to query the survey results directly from the database. For me, having an extensive experience in Business Intelligence and database disclosure, this was a logic step to try and get out the data.
Questions
Example for survey 821161
SELECT 'Q' AS label, grp.gid, grp.group_name, grp.group_order, q.parent_qid, q.question_order, q.qid, q.question, q.help, q.mandatory FROM `lime_questions` AS q INNER JOIN `lime_groups` AS grp ON q.sid = grp.sid AND q.gid = grp.gid WHERE q.sid = 821161 and parent_qid = 0 UNION SELECT 'SQ' AS label, grp.gid, grp.group_name, grp.group_order, q.parent_qid, q.question_order, q.qid, q.question, q.help, q.mandatory FROM `lime_questions` AS q INNER JOIN `lime_groups` AS grp ON q.sid = grp.sid AND q.gid = grp.gid WHERE q.sid = 821161 and parent_qid != 0 UNION SELECT 'SQ' AS label, grp.gid, grp.group_name, grp.group_order, q.qid, 99 AS question_order, q.qid, 'Anders' AS question, q.help, q.mandatory FROM `lime_questions` AS q INNER JOIN `lime_groups` AS grp ON q.sid = grp.sid AND q.gid = grp.gid WHERE q.sid = 821161 and parent_qid = 0 AND type = 'M' and other = 'Y' ORDER BY 1 ASC, 4, 5, 6
Responses
Example for survey 821161
SELECT CONCAT("821161_", s.id) AS UNIEK_SID, p.token, s.seed, p.firstname, p.lastname, p.email, p.completed, s.ipaddr, s.821161X70X1158SQ001, s.821161X70X1158SQ003, s.821161X70X1158SQ004, s.821161X70X1158SQ005, s.821161X70X1158SQ006, s.821161X70X1158other, s.821161X71X1165, s.821161X71X1159, s.821161X71X1160SQ001, s.821161X71X1160SQ006, s.821161X71X1160SQ007, s.821161X71X1160SQ004, s.821161X71X1160other, s.821161X71X1161, s.821161X71X1163, s.821161X71X1162, s.821161X71X1162_filecount, s.821161X72X1164, s.821161X72X1166, s.821161X72X1167 FROM `lime_survey_821161` AS s INNER JOIN `lime_tokens_821161` AS p on s.token = p.token
Generate survey table
Example for survey 233669
SELECT CONCAT('lime_survey_', q.sid) AS table_name, CONCAT(q.sid , 'X', q.gid, 'X', (CASE WHEN q.parent_qid = 0 THEN q.qid ELSE q.parent_qid END), (CASE WHEN q.parent_qid <> 0 THEN q.title ELSE '' END) ) AS column_name, (grp.group_order * 100000) + (q.question_order * 1000) AS ORD, 'Y??' AS column_exists, q.question AS DESCR, q.* FROM `lime_questions` q INNER JOIN `lime_groups` grp ON q.sid = grp.sid AND q.gid = grp.gid WHERE q.sid = 233669 AND q.type NOT IN ('M','R','P','T','O','S','|') ## Hierboven komt niets meer uit, als het goed is! UNION # Multiple choice subquestions SELECT CONCAT('lime_survey_', q.sid) AS table_name, CONCAT(q.sid , 'X', q.gid, 'X', (CASE WHEN q.parent_qid = 0 THEN q.qid ELSE q.parent_qid END), (CASE WHEN q.parent_qid <> 0 THEN q.title ELSE '' END) ) AS column_name, (grp.group_order * 100000) + (pq.question_order * 1000) + (q.question_order * 10) AS ORD, 'Y' AS column_exists, q.question AS DESCR, q.* FROM `lime_questions` q INNER JOIN `lime_groups` grp ON q.sid = grp.sid AND q.gid = grp.gid INNER JOIN `lime_questions` pq ON q.parent_qid = pq.qid WHERE q.sid = 233669 AND q.type IN ('T') UNION # Multiple choice with comments SELECT CONCAT('lime_survey_', q.sid) AS table_name, CONCAT(q.sid , 'X', q.gid, 'X', q.qid, sq.title, 'comment') AS column_name, (grp.group_order * 100000) + (q.question_order * 1000) + (sq.question_order * 10) + 1 AS ORD, 'Y' AS column_exists, q.question AS DESCR, sq.* FROM `lime_questions` q INNER JOIN `lime_groups` grp ON q.sid = grp.sid AND q.gid = grp.gid INNER JOIN `lime_questions` sq ON q.sid = sq.sid AND q.qid = sq.parent_qid AND q.type = 'P' AND sq.type = 'T' WHERE q.sid = 233669 UNION # Multiple choice questions which have option 'Other' SELECT CONCAT('lime_survey_', q.sid) AS table_name, CONCAT(q.sid , 'X', q.gid, 'X', (CASE WHEN q.parent_qid = 0 THEN q.qid ELSE q.parent_qid END), 'other') AS column_name, (grp.group_order * 100000) + (q.question_order * 1000) + 99 AS ORD, 'Y' AS column_exists, 'Anders' AS DESCR, q.* FROM `lime_questions` q INNER JOIN `lime_groups` grp ON q.sid = grp.sid AND q.gid = grp.gid WHERE q.sid = 233669 AND q.other = 'Y' UNION # File upload fields SELECT CONCAT('lime_survey_', q.sid) AS table_name, CONCAT(q.sid , 'X', q.gid, 'X', (CASE WHEN q.parent_qid = 0 THEN q.qid ELSE q.parent_qid END), '_filecount') AS column_name, (grp.group_order * 100000) + (q.question_order * 1000) + 1 AS ORD, 'Y' AS column_exists, q.question AS DESCR, q.* FROM `lime_questions` q INNER JOIN `lime_groups` grp ON q.sid = grp.sid AND q.gid = grp.gid WHERE q.sid = 233669 AND q.type = '|' UNION # Ranking fields SELECT CONCAT('lime_survey_', q.sid) AS table_name, CONCAT(q.sid , 'X', q.gid, 'X', (CASE WHEN q.parent_qid = 0 THEN q.qid ELSE q.parent_qid END), a.sortorder) AS column_name, (grp.group_order * 100000) + (q.question_order * 1000) + (a.sortorder * 10) AS ORD, 'Y' AS column_exists, a.answer AS DESCR, q.* FROM `lime_questions` q INNER JOIN `lime_groups` grp ON q.sid = grp.sid AND q.gid = grp.gid INNER JOIN `lime_answers` a ON q.qid = a.qid WHERE q.sid = 233669 AND q.type = 'R' UNION # Radio button SELECT CONCAT('lime_survey_', q.sid) AS table_name, CONCAT(q.sid , 'X', q.gid, 'X', q.qid) AS column_name, (grp.group_order * 100000) + (q.question_order * 1000) AS ORD, 'Y' AS column_exists, q.question AS DESCR, q.* FROM `lime_questions` q INNER JOIN `lime_groups` grp ON q.sid = grp.sid AND q.gid = grp.gid WHERE q.sid = 233669 AND q.type = 'O' UNION # Radio button - all answers SELECT CONCAT('lime_survey_', q.sid) AS table_name, CONCAT(q.sid , 'X', q.gid, 'X', q.qid) AS column_name, (grp.group_order * 100000) + (q.question_order * 1000) + (a.sortorder * 10) + 5 AS ORD, 'N' AS column_exists, CONCAT(a.code, '|', a.answer) AS DESCR, q.* FROM `lime_questions` q INNER JOIN `lime_groups` grp ON q.sid = grp.sid AND q.gid = grp.gid INNER JOIN `lime_answers` a ON q.qid = a.qid WHERE q.sid = 233669 AND q.type = 'O' UNION # Radio button with comments SELECT CONCAT('lime_survey_', q.sid) AS table_name, CONCAT(q.sid , 'X', q.gid, 'X', q.qid, 'comment') AS column_name, (grp.group_order * 100000) + (q.question_order * 1000) + 1 AS ORD, 'Y' AS column_exists, q.question AS DESCR, q.* FROM `lime_questions` q INNER JOIN `lime_groups` grp ON q.sid = grp.sid AND q.gid = grp.gid WHERE q.sid = 233669 AND q.type = 'O' UNION # Short text (or location) or Upload SELECT CONCAT('lime_survey_', q.sid) AS table_name, CONCAT(q.sid , 'X', q.gid, 'X', (CASE WHEN q.parent_qid = 0 THEN q.qid ELSE q.parent_qid END), (CASE WHEN q.parent_qid <> 0 THEN q.title ELSE '' END) ) AS column_name, (grp.group_order * 100000) + (q.question_order * 1000) AS ORD, 'Y' AS column_exists, q.question AS DESCR, q.* FROM `lime_questions` q INNER JOIN `lime_groups` grp ON q.sid = grp.sid AND q.gid = grp.gid WHERE q.sid = 233669 AND q.type IN ('S','|') UNION # Questions with subquestions, ranking or ... # Do NOT exist as columns SELECT CONCAT('lime_survey_', q.sid) AS table_name, CONCAT(q.sid , 'X', q.gid, 'X', (CASE WHEN q.parent_qid = 0 THEN q.qid ELSE q.parent_qid END), (CASE WHEN q.parent_qid <> 0 THEN q.title ELSE '' END) ) AS column_name, (grp.group_order * 100000) + (q.question_order * 1000) AS ORD, 'N' AS column_exists, q.question AS DESCR, q.* FROM `lime_questions` q INNER JOIN `lime_groups` grp ON q.sid = grp.sid AND q.gid = grp.gid WHERE q.sid = 233669 AND q.type IN ('M','R','P') ORDER BY 1, 3