Back to limesurvey.org

Welcome to the LimeSurvey Community Forum

Ask the community, share ideas, and connect with other LimeSurvey users!

Create more readable SQL VIEW for Limesurvey response table

  • Mazi
  • Mazi's Avatar Topic Author
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
8 years 7 months ago - 8 years 7 months ago #143778 by Mazi
Create more readable SQL VIEW for Limesurvey response table was created by Mazi
Hi everyone,
this problem is really causing me a headache. We have coded a feature to create a MySQL VIEW once a survey gets activated. That VIEW is basically a copy of the lime_survey_123456 table holding the survey responses but it uses the question codes to get more readable headings (see screenshots). That works well and we can easily connect to the VIEW for easier analyzing data using certain tools.
The problem now is that we want to make the answers more readable as well:
  • For array questions for example Limesurvey just stores the answer code -> we also want to add a column for the answer text.
  • For multiple numeric questions Limesurvey stores the number inputted -> we also want to add a column listing the related sub-question text.

We have extended our VIEW feature to add additional columns when creating the VIEW and we have added code to query the related answer and sub-question texts. Those queries work fine, this is what the VIEW shows if ONE data set is inputted (looks good so far):


Thing is that once we add a second data set with different answers, the data gets mixed up. It looks like MySQL just aggregates the data from all rows to fill the additional columns. There are now additional answer/sub-question texts at both rows (marked "?") though for that data set no answer was given, see:


Is it possible to kind of define the scope for the additional "..._TEXT" columns so they get filled based on details of the current row, not the whole data?

For those interested in the underlying SQL code to create the VIEW: pastebin.com/xmP27dsH

Let me know if you have any questions, it is a little complex to describe this issue.
Best regards/Beste Grüße,
Dr. Marcel Minke
survey-consulting.com
offlinesurveys.com
Feel free to contact me by email for professional LimeSurvey support!
Attachments:
Last edit: 8 years 7 months ago by Mazi. Reason: added link to SQL code
The topic has been locked.
More
8 years 7 months ago #143779 by Marernon_25251
Replied by Marernon_25251 on topic Create more readable SQL VIEW for Limesurvey response table
I am also very interested in this.
The topic has been locked.
More
8 years 7 months ago #143837 by Deusdeorum
Replied by Deusdeorum on topic Create more readable SQL VIEW for Limesurvey response table
I can't figure out what you want to do with the IFNULL() statement though?

But if I understand you correctly you want this:


Can you use COALESCE instead within a CASE?
Code:
CREATE VIEW lime_view_925212 AS SELECT s.id 'id',s.submitdate 'submitdate',s.lastpage 'lastpage',s.startlanguage 'startlanguage',
s.925212X1X461 'multiOne',
CASE WHEN (COALESCE(s.925212X1X461, '')= '') THEN NULL ELSE (SELECT q.question from lime_questions q, lime_survey_925212 ls where q.qid=48 AND q.title='1' AND q.language='en' AND q.type='T' LIMIT 1) END AS 'multiOneText',
s.925212X1X462 'multiTwo',
CASE WHEN (COALESCE(s.925212X1X462, '')= '') THEN NULL ELSE (SELECT q.question from lime_questions q, lime_survey_925212 ls where q.qid=49 AND q.title='2' AND q.language='en' AND q.type='T' LIMIT 1) END AS 'multiTwoText',
s.925212X1X463 'multiThree',
CASE WHEN (COALESCE(s.925212X1X463, '')= '') THEN NULL ELSE (SELECT q.question from lime_questions q, lime_survey_925212 ls where q.qid=50 AND q.title='3' AND q.language='en' AND q.type='T' LIMIT 1) END AS 'multiThreeText',
s.925212X1X511 'multiNumOne',
CASE WHEN (COALESCE(s.925212X1X511, '')= '') THEN NULL ELSE (SELECT q.question from lime_questions q, lime_survey_925212 ls where q.qid=53 AND q.title='1' AND q.language='en' AND q.type='T' LIMIT 1) END AS 'multiNumOneText',
s.925212X1X512 'multiNumTwo',
CASE WHEN (COALESCE(s.925212X1X512, '')= '') THEN NULL ELSE (SELECT q.question from lime_questions q, lime_survey_925212 ls where q.qid=54 AND q.title='2' AND q.language='en' AND q.type='T' LIMIT 1) END AS 'multiNumTwoText',
s.925212X1X551 'ArrayQuestion1',
CASE WHEN (COALESCE(s.925212X1X551, '')= '') THEN NULL ELSE (SELECT a.answer from lime_answers a, lime_survey_925212 ls where a.qid=55 AND s.925212X1X551=a.code AND a.language='en' LIMIT 1) END AS 'ArrayQuestion1Text',
s.925212X1X552 'ArrayQuestion2',
CASE WHEN (COALESCE(s.925212X1X552, '')= '') THEN NULL ELSE (SELECT a.answer from lime_answers a, lime_survey_925212 ls where a.qid=55 AND s.925212X1X552=a.code AND a.language='en' LIMIT 1) END AS 'ArrayQuestion2Text'
FROM lime_survey_925212 s

This will create what you are after, might be getting slow with large survey but it works if I understand your question correctly, included the .lss file from which survey I used it on
Attachments:
The topic has been locked.
  • Mazi
  • Mazi's Avatar Topic Author
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
8 years 6 months ago #144458 by Mazi
Replied by Mazi on topic Create more readable SQL VIEW for Limesurvey response table
Hi everyone,

thanks for your valuable feedback. I was finally able to solve this problem based on this feedback and additional hints I had received at Stackoverflow, see stackoverflow.com/questions/40289764/add...ate-more-readable-ve
Best regards/Beste Grüße,
Dr. Marcel Minke
survey-consulting.com
offlinesurveys.com
Feel free to contact me by email for professional LimeSurvey support!
The topic has been locked.
Moderators: tpartner, holch

Lime-years ahead

Online-surveys for every purse and purpose

Pricing & Plans
Get started

Legal

About Us

Open Source