MySQL Nesting Relations

Question

I use backbone and need to nest Answers in Questions and Questions in Categories. My problem is the data I get from MySQL.

I would like to have an array I can easily use with backbone, starting at the top (Category) and nest down to the bottom (Answers).

[Category1: [Question1: [Answer1: {...} ] ] ]

I use the following query to get all my MySQL data:

var getRecord = function(callback) {
  var options = {
    sql: 'SELECT * FROM Categories ' +
         'LEFT JOIN Questions ON Categories.idCategories = Questions.idCategory ' +
         'LEFT JOIN Answers ON Questions.idQuestions = Answers.idQuestion ',
    nestTables: true
  }

  req.app.sql.query(options, function(err, result) {
    if (err)
      return callback(err, null)

    outcome.record = result
    return callback(null, 'done')
  })
}

And the output looks something like this:

[
   0: [CategoryObj, QuestionObj, AnswerObj]
   1: ...
]

The MySQL Node Package does not nest 1:n relations, instead it creates an array with the length of most matches, so in the case I have 2 Categories, with each two Questions, with each two Answers -> Array length of 8, because I have 8 Answers in total. But I cannot nest this array, in backbone collections without writing crazy loops and hacks.

Am I doing something wrong in the query or is there a packages that does the parsing job?

(I'm used to MongoDB (using embedded documents was quite easy) and now I have to use MySQL for this project..)

This is the MySQL Node Package on npm

Answer 1

I ended up parsing it myself. For some reason I was not able to find a well working ORM helper, that could do this job for me. Anyway I tried to avoid this solution, but here you go if you have the same problem one day this might help.

var async = require('async')

var getAnswers = function (id, callback) {
  req.app.sql.query('SELECT * FROM Answers WHERE idQuestion LIKE ?', [id], function(err, result) {
    if (err)
      return callback(err, null)

    return callback(null, result)
  })
}

var getQuestions = function (id, callback) {
  req.app.sql.query('SELECT * FROM Questions WHERE idCategory LIKE ?', [id], function(err, result) {
    if (err)
      return callback(err, null)

    // Pair answers to questions
    async.times(result.length, function(n, next) {
      getAnswers(result[n].idQuestions, function (err, answers) {
        result[n].answers = answers
        next(err, result[n])
      })
    }, function(err, questions) {
      callback(null, questions)
    })
  })
}

var getRecord = function(callback) {
  req.app.sql.query('SELECT * FROM Categories', function(err, result) {
    if (err)
      return callback(err, null)

    // Pair questions to categories
    async.times(result.length, function(n, next) {
      getQuestions(result[n].idCategories, function (err, questions) {
        result[n].questions = questions
        next(err, result[n])
      })
    }, function(err, final) {
      callback(null, final)
    })

  })
}

var asyncFinally = function(err, results) {
  if (err)
    return next(err)

  // we call results[0] because async.times leaves all the categories in there..
  // sendSomewhere( results[0] )
}

async.parallel([getRecord], asyncFinally)