Using subqueries to build nested object in mysql

Question

I'm not sure if this is possible in mysql, but I'm trying to build a nested object out of a query instead of php. I have a db of survey results, and I want to build an object who's keys are the question and value is an array/object of the answers. Is this possible? I'm using something like this:

SELECT 
ss.*,
    (SELECT int_value FROM `SubmittedQuestions` AS su WHERE ss.id = su.submitted_survey_id)
FROM
`SubmittedSurveys` as ss;

Do I have to build this object in PHP? My issue is that I'm doing all these loops in PHP and I think it's taking a while to build the objects whereas if I could do it in mysql I think it'd be one fast query. Let me know what your thoughts are on this issue.

I'm looking for an object like this:

Survey: { Question 1: [ answer 1, answer 2], Question 2: [ answer 1, answer 2] }

So what I'm currently doing in php is querying all survey questions from one table, then with that object, I loop through and query for each question and get the answers from another table. If there are a lot of questions this design will be super slow, can anybody suggest an alternative?

MySQL deals with row-based data, that's it. There is no simple, sane way to nest data the way you are asking. You can use joins, but you will end up with quite a bit of duplicate data to pare out. — Sammitch
– Sammitch, Commented Feb 2, 2015 at 21:49
Ahh thank you, that's what I feared. So would you suggest handling small bits of data at a time? Or maybe display question and when clicked on, do an AJAX call to get it's answers? — Jarryd Goodman
– Jarryd Goodman, Commented Feb 2, 2015 at 21:56
I am a fan of only pulling the minimum amount of data you need when you need it, and caching where possible. — Sammitch
– Sammitch, Commented Feb 2, 2015 at 21:57

Sammitch · Accepted Answer · 2015-02-02 21:55:57Z

7

The way you have described that you're already doing is probably best. You can retrieve everything in one go using JOINs, eg:

SELECT *
FROM surveys s INNER JOIN questions q
  ON s.id = q.survey_id
  INNER JOIN answers a
  ON q.id = a.question_id
WHERE s.id = $submitted_survey_id

And your results will look like:

survey1,question1,answer1
survey1,question1,answer2
survey1,question1,answer3
survey1,question2,answer1
survey1,question2,answer2
survey1,question2,answer3
survey1,question3,answer1
survey1,question3,answer2
survey1,question3,answer3
...

But you'll still have to write logic to pull those results apart into a sane data structure, and you're retrieving a lot of duplicate data from the database.

SQL in general does not produce hierarchical result sets, you have to produce those through your application one way or another.

answered Feb 2, 2015 at 21:55

Sammitch

32.5k7 gold badges58 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using subqueries to build nested object in mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related