node-mysql-nesting0.0.3 • Public • Published
Converts mysql query result array (flat) to nested (multi-dimensional) object.
MySQL returns flat objects. We want to nest joined objects.
Let's say we have courses table, each course belongs to a department and has various course sections. We would like to have a resulting courses array that has a department object property within it and have a list of course sections.
node-mysql library function mysqlConnection.query() takes 2 parameters: sql options and a callback function.
Instead of this:
var options = sql: sqlString nestTables: true ;mysqlConnection;
As you can see, we set nestTables to true. Taken from node-mysql documentation:
By default, node-mysql will overwrite colliding column names in the order the columns are received from MySQL, causing some of the received values to be unavailable. However, you can also specify that you want your columns to be nested below the table name by using nestTable.
After setting nestTables to true, result array will be something like this:
table1:fieldA: '...'fieldB: '...'table2:fieldA: '...'fieldB: '...'...
Here is our SQL statement:
SELECT * FROM coursesLEFT JOIN course_sections ON course_sections.course_id = courses.idLEFT JOIN departments ON departments.id = courses.department_idLEFT JOIN course_terms ON course_sections.courseterm_id = course_terms.id';
You should use joins instead of WHEREs and ANDs in order to get related columns even if they have NULL value.
var nestingOptions =tableName : 'courses' pkey: 'id' fkeys:table:'departments'col:'department_id'tableName : 'course_sections' pkey: 'id' fkeys:table:'courses'col:'course_id'table:'course_terms'col:'courseterm_id'tableName : 'departments' pkey: 'id'tableName : 'course_terms' pkey: 'id';
Please make sure that table names are in the same order as in your SQL statement. Otherwise it won't work.
Now, it is time to use our beloved function!
And, this is it! Try to run example code to see the result.
To illustrate how this function works, I included a simple application to the package.
- node.js express app (under /server directory)
- mysql database (node-mysql-nested.sql)
Create a new database and import sql/node-mysql-nesting.sql.
Rename mysql_config.example.js to mysql_config.js and update it with your mysql configuration
npm install. This will install express and node-mysql.
Start the node server:
node app.js(if you don't have nodejs installed, go to: http://nodejs.org/)
Now, open your browser and go to
http://localhost:8081. You should be able to see a nested result object.