Table of Contents

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