CODEWITHSUNDEEP
node.jsexpresssequelize.js
msthakur0064
msthakur0064

Reputation: 1465

How to get join data result without prefix table name in Sequelize ORM

I am using sequelize ORM in node js. I am join two table and get result but that result return with table name as prefix.

var Sequelize = require('sequelize');
var sequelize = new Sequelize('test', 'root', '', {
    // configuration
  }
});

const db = {};

db.Sequelize = Sequelize;  
db.sequelize = sequelize;

db.role = require('./../model/definitions/role')(sequelize, Sequelize);  
db.admin = require('./../model/definitions/admin')(sequelize, Sequelize);  

  db.admin.findAll({ 
    include: [{ 
      model: db.role,                      
      where:{status : 'Active'},     
    }],
    raw: true      

  }).then(function(result) {
      console.log(result);
  }).catch(function(error) {
    console.log(error);
  }).done();

Now I am getting this result:

[{
    "id": 36,                
    "email": "[email protected]",
    "username": "test",
    "status": "Active",
    "role.role_id": 1,
    "role.role_name": "Admin"
}]

but I need this result:

[{
    "id": 36,                
    "email": "[email protected]",
    "username": "test",
    "status": "Active",
    "role_id": 1,
    "role_name": "Admin"
}]

so, how to remove prefix table name 'role' from column. I have only need 'role_id' or 'role_name' not need this type data like 'role.role_id', 'role.role_name'

Upvotes: 21

Views: 10709

Answers (4)

sishanov
sishanov

Reputation: 171

This should work

A is a table that is associated to B using a column called bId so B has a primary key of Id which is associates to a bid column in A table that is shown in the standard result set as b.id but we want these two column to be called as Id & Name

A.belongsTo(B);
        return await A.findAll({
            attributes: [
                [Sequelize.col('b.id'), 'Id'],
                [Sequelize.col('b.name'), 'Name']
            ],
            raw: true,
            where: { /*Some condition*/ },
            include: {
                model: B,
                attributes: [],
                required: true
            },
        });

Upvotes: 5

spacedev
spacedev

Reputation: 1190

The top level model also can be modified to exclude and include the properties if the query still want to use along with 

raw: true

 db.admin.findAll({ 
    include: [{ 
      model: db.role,                      
      where:{status : 'Active'},
      attributes: ["role_id", "role_name"],  
      nested: false,  
    }],
    attributes: {exclude: [],
                 include: ["role.role_id", "role.role_name"]},
    raw: true      
  });

additionally you can have aliases as well

 db.admin.findAll({ 
    include: [{ 
      model: db.role,                      
      where:{status : 'Active'},
      attributes: [["role_id", "roleId"], ["role_name", "roleName"]] 
    }],
    attributes: {exclude: [],
                 include: ["role.roleId", "role.roleName"]},
    raw: true      
  });

Upvotes: 1

Mayur
Mayur

Reputation: 770

This should work,

db.admin.findAll({ 
    attributes: ['id', 'username', 'email', 'status', 'role.role_id', 'role.role_name'],
    include: [{ 
      model: db.role,                      
      where:{status : 'Active'}, 
      attributes: []
    }],
    raw: true
})

I found this here

Upvotes: 32

Vivek Doshi
Vivek Doshi

Reputation: 58593

raw: true , causes the issue while returning data with associations , solution to this is :

Remove :

raw: true

From :

db.admin.findAll({ 
    include: [{ 
      model: db.role,                      
      where:{status : 'Active'},     
    }],
    raw: true // <--------- Remove this   
})

Upvotes: 0

Related Questions