How to get two tables data in Node.js with object inside other object

Question

I have two tables and I need data in this format. How is this Possible?

Required Output

{
  "id":"1",
  "name":"akhil",
  "pics": [
    {
      "pic1": "123.jpg",
      "pic2": "123.jpg"
    }
  ]
}

Generally I use this for getting data from single table

const express = require('express');
const app = express();
const jwt = require('jsonwebtoken');
const bcrypt = require('bcryptjs');
const config = require('./config');

var VerifyToken = require('./VerifyToken');

const mysql      = require('mysql');

app.use(express.json());
const connection = mysql.createConnection({
   host     : 'localhost',
   user     : 'root',
   password : 'password',
   database : 'sample'
});
    app.get('/usersdata', VerifyToken, (req, res) => {
       let id = req.userId;
       console.log(req.userId);
       connection.query("select * from users", function (error, results, fields) {
          if (error) throw error;
          else {
             res.send({"result": results});
          }
       });
    })

What is `connection.query`? What module are you using to access your database? — Jordan Running, Apr 02 '19 at 18:41

score 1 · Answer 1 · answered Apr 03 '19 at 02:28

My Solution:

app.get('/usersdata', (req, res) => {
   connection.query("select u.id, u.name, p.pic1, p.pic2 from users u, pics p where u.usersid=p.id", function (error, results, fields) {
      if (error) throw error;
      else {
         let data = results;
         let newResult = {};
         results.map(row => {
            if(newResult[row.id]) {
               newResult[row.id].pics.push(row.pic1, row.pic2)
            } else {
               newResult[row.id] = { id: row.id, name: row.name, pics: [row.pic1, row.pic2] };
            }
         })
         res.send({ "result": Object.values(newResult) });
      }
   });
})

score 0 · Answer 2 · answered Apr 02 '19 at 18:21

0

I would use an ORM instead of writing query myself. Check this link used for a project saved lot of time and code was cleaner.

answered Apr 02 '19 at 18:21

Bharath

390
1
6

See this [link](https://stackoverflow.com/questions/20460270/how-to-make-join-queries-using-sequelize-on-node-js) for joining two tables in Sequelize – Bharath Apr 02 '19 at 18:29
This is my client requirement and I cannot change it to some ORM @Bharath – GRANDHI VENKATA SAI AKHIL Apr 03 '19 at 01:08

How to get two tables data in Node.js with object inside other object

2 Answers2