Querying Survey fields and responses

[sp-jordan-violet (Jordan Violet)]

I’m using this Discourse Surveys plugin: GitHub - discourse/discourse-surveys

Upon submitting surveys, I see that radio buttons, checkboxes, and dropdowns seem to register as NULL in the database. It also seems to have registered one answer twice:

[isaac (Isaac Janzen)]

The value for

@sp-jordan-violet

radio buttons, checkboxes, and dropdowns

are stored in a separate table.

This should get you were you need to be

-- [params]
-- null int :user_id
-- string :name

SELECT s.name, s.post_id, sf.question, COALESCE(sr.value, sfo.html) AS value, sr.user_id, sr.created_at as responded_at
FROM surveys s
JOIN survey_fields sf ON sf.survey_id = s.id
JOIN survey_responses sr ON sr.survey_field_id = sf.id
LEFT JOIN survey_field_options sfo ON sfo.id = sr.survey_field_option_id
WHERE s.name = :name
AND (:user_id IS NULL OR sr.user_id = :user_id)
ORDER BY sr.user_id

I have added a per user query option (when left empty it queries all user per survey), but feel free to drop

AND (:user_id IS NULL OR sr.user_id = :user_id)

if you are not interested in that.

[sp-jordan-violet (Jordan Violet)]

Ah, I didn’t even catch that extra table! Thank you!