I am switching from using node-mssql to Sequelize in order to access my database in a simpler fashion. As a newbie to sequelize I am stumbling my way through pulling the correct data.
As I am converting a .net site with .net authentication to a node site I am using the existing authentication database. Currently I am trying to pull all roles for an existing user.
Here is the code I have so far. It returns both userID and roleID along with the username and role name that I desire. How can I remove these 2 ID columns from my query results?
test.aspnet_Users.findAll({
logging: console.log,
where: { LoweredUserName: `mcad2\\${user}` },
attributes: ['LoweredUserName'],
include: {
model: test.aspnet_Roles,
as: 'RoleId_aspnet_Roles',
attributes: ['LoweredRoleName']
}
}).then(user => {
console.log('\n\n' + JSON.stringify(user))
})
The database is set up so that both userID and roleID are contained in a third table, aspnet_UsersInRoles. This is a relatively simple 2 join query but I am not certain how to make it work using sequelize.
Here is that sequelize-auto code which was created:
const Sequelize = require('sequelize');
module.exports = function(sequelize, DataTypes) {
return sequelize.define('aspnet_Roles', {
ApplicationId: {
type: DataTypes.UUID,
allowNull: false,
references: {
model: 'aspnet_Applications',
key: 'ApplicationId'
}
},
RoleId: {
type: DataTypes.UUID,
allowNull: false,
primaryKey: true
},
RoleName: {
type: DataTypes.STRING(256),
allowNull: false
},
LoweredRoleName: {
type: DataTypes.STRING(256),
allowNull: false
},
Description: {
type: DataTypes.STRING(256),
allowNull: true
}
}, {
sequelize,
tableName: 'aspnet_Roles',
schema: 'dbo',
timestamps: false,
indexes: [
{
name: "aspnet_Roles_index1",
unique: true,
fields: [
{ name: "ApplicationId" },
{ name: "LoweredRoleName" },
]
},
{
name: "PK__aspnet_Roles__31EC6D26",
unique: true,
fields: [
{ name: "RoleId" },
]
},
]
});
};
const Sequelize = require('sequelize');
module.exports = function(sequelize, DataTypes) {
return sequelize.define('aspnet_Users', {
ApplicationId: {
type: DataTypes.UUID,
allowNull: false,
references: {
model: 'aspnet_Applications',
key: 'ApplicationId'
}
},
UserId: {
type: DataTypes.UUID,
allowNull: false,
primaryKey: true
},
UserName: {
type: DataTypes.STRING(256),
allowNull: false
},
LoweredUserName: {
type: DataTypes.STRING(256),
allowNull: false
},
MobileAlias: {
type: DataTypes.STRING(16),
allowNull: true
},
IsAnonymous: {
type: DataTypes.BOOLEAN,
allowNull: false,
defaultValue: false
},
LastActivityDate: {
type: DataTypes.DATE,
allowNull: false
}
}, {
sequelize,
tableName: 'aspnet_Users',
schema: 'dbo',
timestamps: false,
indexes: [
{
name: "aspnet_Users_Index",
unique: true,
fields: [
{ name: "ApplicationId" },
{ name: "LoweredUserName" },
]
},
{
name: "aspnet_Users_Index2",
fields: [
{ name: "ApplicationId" },
{ name: "LastActivityDate" },
]
},
{
name: "PK__aspnet_Users__03317E3D",
unique: true,
fields: [
{ name: "UserId" },
]
},
]
});
};
const Sequelize = require('sequelize');
module.exports = function(sequelize, DataTypes) {
return sequelize.define('aspnet_UsersInRoles', {
UserId: {
type: DataTypes.UUID,
allowNull: false,
primaryKey: true,
references: {
model: 'aspnet_Users',
key: 'UserId'
}
},
RoleId: {
type: DataTypes.UUID,
allowNull: false,
primaryKey: true,
references: {
model: 'aspnet_Roles',
key: 'RoleId'
}
}
}, {
sequelize,
tableName: 'aspnet_UsersInRoles',
schema: 'dbo',
timestamps: false,
indexes: [
{
name: "aspnet_UsersInRoles_index",
fields: [
{ name: "RoleId" },
]
},
{
name: "PK__aspnet_UsersInRo__35BCFE0A",
unique: true,
fields: [
{ name: "UserId" },
{ name: "RoleId" },
]
},
]
});
};
var DataTypes = require("sequelize").DataTypes;
var _aspnet_Applications = require("./aspnet_Applications");
var _aspnet_Membership = require("./aspnet_Membership");
var _aspnet_Paths = require("./aspnet_Paths");
var _aspnet_PersonalizationAllUsers = require("./aspnet_PersonalizationAllUsers");
var _aspnet_PersonalizationPerUser = require("./aspnet_PersonalizationPerUser");
var _aspnet_Profile = require("./aspnet_Profile");
var _aspnet_Roles = require("./aspnet_Roles");
var _aspnet_SchemaVersions = require("./aspnet_SchemaVersions");
var _aspnet_Users = require("./aspnet_Users");
var _aspnet_UsersInRoles = require("./aspnet_UsersInRoles");
var _aspnet_WebEvent_Events = require("./aspnet_WebEvent_Events");
var _aspnet_ZoneNumbers = require("./aspnet_ZoneNumbers");
var _aspnet_ZonePositions = require("./aspnet_ZonePositions");
function initModels(sequelize) {
var aspnet_Applications = _aspnet_Applications(sequelize, DataTypes);
var aspnet_Membership = _aspnet_Membership(sequelize, DataTypes);
var aspnet_Paths = _aspnet_Paths(sequelize, DataTypes);
var aspnet_PersonalizationAllUsers = _aspnet_PersonalizationAllUsers(sequelize, DataTypes);
var aspnet_PersonalizationPerUser = _aspnet_PersonalizationPerUser(sequelize, DataTypes);
var aspnet_Profile = _aspnet_Profile(sequelize, DataTypes);
var aspnet_Roles = _aspnet_Roles(sequelize, DataTypes);
var aspnet_SchemaVersions = _aspnet_SchemaVersions(sequelize, DataTypes);
var aspnet_Users = _aspnet_Users(sequelize, DataTypes);
var aspnet_UsersInRoles = _aspnet_UsersInRoles(sequelize, DataTypes);
var aspnet_WebEvent_Events = _aspnet_WebEvent_Events(sequelize, DataTypes);
var aspnet_ZoneNumbers = _aspnet_ZoneNumbers(sequelize, DataTypes);
var aspnet_ZonePositions = _aspnet_ZonePositions(sequelize, DataTypes);
aspnet_Roles.belongsToMany(aspnet_Users, { as: 'UserId_aspnet_Users', through: aspnet_UsersInRoles, foreignKey: "RoleId", otherKey: "UserId" });
aspnet_Users.belongsToMany(aspnet_Roles, { as: 'RoleId_aspnet_Roles', through: aspnet_UsersInRoles, foreignKey: "UserId", otherKey: "RoleId" });
aspnet_Membership.belongsTo(aspnet_Applications, { as: "Application", foreignKey: "ApplicationId"});
aspnet_Applications.hasMany(aspnet_Membership, { as: "aspnet_Memberships", foreignKey: "ApplicationId"});
aspnet_Paths.belongsTo(aspnet_Applications, { as: "Application", foreignKey: "ApplicationId"});
aspnet_Applications.hasMany(aspnet_Paths, { as: "aspnet_Paths", foreignKey: "ApplicationId"});
aspnet_Roles.belongsTo(aspnet_Applications, { as: "Application", foreignKey: "ApplicationId"});
aspnet_Applications.hasMany(aspnet_Roles, { as: "aspnet_Roles", foreignKey: "ApplicationId"});
aspnet_Users.belongsTo(aspnet_Applications, { as: "Application", foreignKey: "ApplicationId"});
aspnet_Applications.hasMany(aspnet_Users, { as: "aspnet_Users", foreignKey: "ApplicationId"});
aspnet_PersonalizationAllUsers.belongsTo(aspnet_Paths, { as: "Path", foreignKey: "PathId"});
aspnet_Paths.hasOne(aspnet_PersonalizationAllUsers, { as: "aspnet_PersonalizationAllUser", foreignKey: "PathId"});
aspnet_PersonalizationPerUser.belongsTo(aspnet_Paths, { as: "Path", foreignKey: "PathId"});
aspnet_Paths.hasMany(aspnet_PersonalizationPerUser, { as: "aspnet_PersonalizationPerUsers", foreignKey: "PathId"});
aspnet_UsersInRoles.belongsTo(aspnet_Roles, { as: "Role", foreignKey: "RoleId"});
aspnet_Roles.hasMany(aspnet_UsersInRoles, { as: "aspnet_UsersInRoles", foreignKey: "RoleId"});
aspnet_Membership.belongsTo(aspnet_Users, { as: "User", foreignKey: "UserId"});
aspnet_Users.hasOne(aspnet_Membership, { as: "aspnet_Membership", foreignKey: "UserId"});
aspnet_PersonalizationPerUser.belongsTo(aspnet_Users, { as: "User", foreignKey: "UserId"});
aspnet_Users.hasMany(aspnet_PersonalizationPerUser, { as: "aspnet_PersonalizationPerUsers", foreignKey: "UserId"});
aspnet_Profile.belongsTo(aspnet_Users, { as: "User", foreignKey: "UserId"});
aspnet_Users.hasOne(aspnet_Profile, { as: "aspnet_Profile", foreignKey: "UserId"});
aspnet_UsersInRoles.belongsTo(aspnet_Users, { as: "User", foreignKey: "UserId"});
aspnet_Users.hasMany(aspnet_UsersInRoles, { as: "aspnet_UsersInRoles", foreignKey: "UserId"});
return {
aspnet_Applications,
aspnet_Membership,
aspnet_Paths,
aspnet_PersonalizationAllUsers,
aspnet_PersonalizationPerUser,
aspnet_Profile,
aspnet_Roles,
aspnet_SchemaVersions,
aspnet_Users,
aspnet_UsersInRoles,
aspnet_WebEvent_Events,
aspnet_ZoneNumbers,
aspnet_ZonePositions,
};
}
module.exports = initModels;
module.exports.initModels = initModels;
module.exports.default = initModels;
You can add an "exclude" option in the attribute option of the query and exclude the given columns:
test.aspnet_Users.findAll({
logging: console.log,
where: { LoweredUserName: `mcad2\\${user}` },
attributes: { exclude: ['userID'], include: ['LoweredUserName'] },
include: {
model: test.aspnet_Roles,
as: 'RoleId_aspnet_Roles',
attributes: { exclude: ['roleID'], include: ['LoweredRoleName'] },
}
}).then(user => {
console.log('\n\n' + JSON.stringify(user))
})
If I understood your structure correctly this should exclude userID and roleID in the fetched data.