MySQL Nesting Relations

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

There is nothing wrong with the package or how you use it. It just gives you the results returned by MySQL. As you probably know, MySQL itself does not format its results in a "nested" way when you're dealing with 1:n relations. If you use JOINs, it will give you a table with a row for each result it found. As it's a "table-formated" result, all rows have the same number of cells.

You can try to see the result of your request in PHPmyAdmin for example.

Thus, you have to post-format the results. There are probably modules to do that, but I have never used one yet.

If you want to do it yourself, you could do something like :

var nestedResult = {};
result.forEach(function(val){
   var category = val[0],
       question = val[1],
       answer = val[2];
   if (!nestedResult[category]){
       nestedResult[category] = {};
   }
   if (!nestedResult[category][question]){
       nestedResult[category][question] = [];
   }
   nestedResult[category][question].push(answer);
});

Which will give you something like :

{
  "mysql" : {
      "what is JOIN" : ["answer 1 blabla....","answer 2 blabla"],
      "innoDB vs MyISAM" : ["answer 1","answer 2"]
  },
  "php" : {
      "why no php 6 ?" : ["answeeeerr"]
  }
}

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)