admin管理员组文章数量:1297011
I use Sequelize to link the database to my application. But I recently encountered the above bug. It says everywhere that it is a database error. Since both tables have the same primary key. In SQL this is solved with aliases. But in documentation to Sequelize I haven't found solution to this problem. What should I do ?
const { DataTypes } = require("sequelize");
const db = require("../config/database");
const Office = db.define(
"Office",
{
id: {
type: DataTypes.INTEGER,
allowNull: false,
primaryKey: true,
autoIncrement: true,
},
address: DataTypes.STRING(45),
},
{
// Other model options go here
tableName: "office",
timestamps: false,
}
);
const Employees = db.define(
"Employees",
{
id: {
type: DataTypes.INTEGER,
allowNull: false,
primaryKey: true,
autoIncrement: true,
},
name: DataTypes.STRING(45),
job: DataTypes.STRING(45),
reg_date: DataTypes.DATEONLY,
salary: DataTypes.DECIMAL(10, 2),
weekend: DataTypes.INTEGER,
office_id: { type: DataTypes.INTEGER, allowNull: false },
},
{
// Other model options go here
tableName: "employees",
timestamps: false,
}
);
Employees.belongsTo(Office, { foreignKey: "office_id" }); // Foreign key
const Developer = db.define(
"Developer",
{
id: {
type: DataTypes.INTEGER,
allowNull: false,
primaryKey: true,
autoIncrement: true,
},
role: DataTypes.STRING(45),
level: DataTypes.STRING(45),
project_count: DataTypes.INTEGER,
},
{
// Other model options go here
tableName: "developer",
timestamps: false,
}
);
// Developer.belongsTo(Employees);
Employees.hasOne(Developer, { foreignKey: "id", targetKey: "id" }); // Foreign key
const Clients = db.define(
"Clients",
{
id: {
type: DataTypes.INTEGER,
allowNull: false,
primaryKey: true,
autoIncrement: true,
},
name: DataTypes.STRING(45),
total_sum: DataTypes.DECIMAL(20, 2),
},
{
// Other model options go here
tableName: "clients",
timestamps: false,
}
);
const Projects = db.define(
"Projects",
{
id: {
type: DataTypes.INTEGER,
allowNull: false,
primaryKey: true,
autoIncrement: true,
},
price: DataTypes.DECIMAL(15, 2),
started: DataTypes.DATEONLY,
ended: DataTypes.DATEONLY,
teamlead_id: { type: DataTypes.INTEGER, allowNull: false },
designer_id: { type: DataTypes.INTEGER, allowNull: false },
programmer_id: { type: DataTypes.INTEGER, allowNull: false },
dbarch_id: { type: DataTypes.INTEGER, allowNull: false },
client_id: { type: DataTypes.INTEGER, allowNull: false },
},
{
// Other model options go here
tableName: "projects",
timestamps: false,
}
);
Developer.hasOne(Projects, { foreignKey: "teamlead_id" }); // Foreign key
Developer.hasOne(Projects, { foreignKey: "designer_id" }); // Foreign key
Developer.hasOne(Projects, { foreignKey: "programmer_id" }); // Foreign key
Developer.hasOne(Projects, { foreignKey: "dbarch_id" }); // Foreign key
Clients.hasOne(Projects, { foreignKey: "client_id" }); // Foreign key
module.exports = { Clients, Developer, Employees, Office, Projects };
And express request:
app.get("/office_dev_workers_spec_count", (req, res) => {
Employees.findAll({
include: {
model: Developer,
where: {
"$Developer.role$": req.query.dev,
"$Developer.level$": req.query.lvl,
"$Employees.office_id$": req.query.office,
},
attributes: [
[sequelize.fn("COUNT", sequelize.col("id")), "n_devEmployees"],
],
},
}).then((result) => {
res.send(result);
});
});
I use Sequelize to link the database to my application. But I recently encountered the above bug. It says everywhere that it is a database error. Since both tables have the same primary key. In SQL this is solved with aliases. But in documentation to Sequelize I haven't found solution to this problem. What should I do ?
const { DataTypes } = require("sequelize");
const db = require("../config/database");
const Office = db.define(
"Office",
{
id: {
type: DataTypes.INTEGER,
allowNull: false,
primaryKey: true,
autoIncrement: true,
},
address: DataTypes.STRING(45),
},
{
// Other model options go here
tableName: "office",
timestamps: false,
}
);
const Employees = db.define(
"Employees",
{
id: {
type: DataTypes.INTEGER,
allowNull: false,
primaryKey: true,
autoIncrement: true,
},
name: DataTypes.STRING(45),
job: DataTypes.STRING(45),
reg_date: DataTypes.DATEONLY,
salary: DataTypes.DECIMAL(10, 2),
weekend: DataTypes.INTEGER,
office_id: { type: DataTypes.INTEGER, allowNull: false },
},
{
// Other model options go here
tableName: "employees",
timestamps: false,
}
);
Employees.belongsTo(Office, { foreignKey: "office_id" }); // Foreign key
const Developer = db.define(
"Developer",
{
id: {
type: DataTypes.INTEGER,
allowNull: false,
primaryKey: true,
autoIncrement: true,
},
role: DataTypes.STRING(45),
level: DataTypes.STRING(45),
project_count: DataTypes.INTEGER,
},
{
// Other model options go here
tableName: "developer",
timestamps: false,
}
);
// Developer.belongsTo(Employees);
Employees.hasOne(Developer, { foreignKey: "id", targetKey: "id" }); // Foreign key
const Clients = db.define(
"Clients",
{
id: {
type: DataTypes.INTEGER,
allowNull: false,
primaryKey: true,
autoIncrement: true,
},
name: DataTypes.STRING(45),
total_sum: DataTypes.DECIMAL(20, 2),
},
{
// Other model options go here
tableName: "clients",
timestamps: false,
}
);
const Projects = db.define(
"Projects",
{
id: {
type: DataTypes.INTEGER,
allowNull: false,
primaryKey: true,
autoIncrement: true,
},
price: DataTypes.DECIMAL(15, 2),
started: DataTypes.DATEONLY,
ended: DataTypes.DATEONLY,
teamlead_id: { type: DataTypes.INTEGER, allowNull: false },
designer_id: { type: DataTypes.INTEGER, allowNull: false },
programmer_id: { type: DataTypes.INTEGER, allowNull: false },
dbarch_id: { type: DataTypes.INTEGER, allowNull: false },
client_id: { type: DataTypes.INTEGER, allowNull: false },
},
{
// Other model options go here
tableName: "projects",
timestamps: false,
}
);
Developer.hasOne(Projects, { foreignKey: "teamlead_id" }); // Foreign key
Developer.hasOne(Projects, { foreignKey: "designer_id" }); // Foreign key
Developer.hasOne(Projects, { foreignKey: "programmer_id" }); // Foreign key
Developer.hasOne(Projects, { foreignKey: "dbarch_id" }); // Foreign key
Clients.hasOne(Projects, { foreignKey: "client_id" }); // Foreign key
module.exports = { Clients, Developer, Employees, Office, Projects };
And express request:
app.get("/office_dev_workers_spec_count", (req, res) => {
Employees.findAll({
include: {
model: Developer,
where: {
"$Developer.role$": req.query.dev,
"$Developer.level$": req.query.lvl,
"$Employees.office_id$": req.query.office,
},
attributes: [
[sequelize.fn("COUNT", sequelize.col("id")), "n_devEmployees"],
],
},
}).then((result) => {
res.send(result);
});
});
2 Answers
Reset to default 8Both of your tables contain the field id so in the COUNT function SQL does not know which id to use. Try to specifiy ID in sequelize.col("id"). For example sequelize.col("Employees.id").
in my case [SOLVED] .... so we can ignore the ambigous column in some condition here is example
i have user table ( here is a users )
id
full_name
i have driver table ( here is a person )
id
user_id
full_name
when we load the driver table like these
...
await driver.findAndCountAll({
include: [
{
model: user, // here is problem its would like to ambiguous column
paranoid: false,
},
],
where: arrayOfSearch // here is problem that trigger ambigious column // if you didnt implement to search its work.
...
...
then why we got ambigous ? here is why ( by the war advanceSearchCondition is arrayOfSearch ya !!! )
// /api/driver?keyword=full_name%3DA%26phone%3D2
if(keyword){
let keywordSplit = keyword.split('&');
keywordSplit.map(el => {
const data = el.split("=");
if (data[0] == 'created_at') {
advanceSearchCondition.push({
created_at: {
[Op.substring]: `${data[1]}`
}
});
} else if (data[0] == 'updated_at') {
advanceSearchCondition.push({
updated_at: {
[Op.substring]: `${data[1]}`
}
});
} else if (data[0] == 'full_name') { // if you didnt code like these you will get ambigous column for full_name
advanceSearchCondition.push({
full_name: {
[Op.like]: Sequelize.literal(`\'%${data[1]}%\'`)
}
});
} else if (data[0] == 'phone') {
advanceSearchCondition.push({
phone: {
[Op.like]: Sequelize.literal(`\'%${data[1]}%\'`)
}
});
} else { // because you make code like these
winLogger.error(data[0]);
winLogger.error(data[1]);
advanceSearchCondition.push({
[`$${data[0]}$`]: {
[Op.substring]: `${data[1]}`
}
});
}
})
}
explanation... in else condition i search by $driver.full_name$ so i dont need to pass in include / associate where properties... we can put the search in outside of include but it would ambigous when other associate have same column and you implement search with $table.column$ to put outside the include.
本文标签：
版权声明：本文标题：javascript - Sequelize error: column reference "id" is ambiguous. Can't find answer in documentation - 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1741647918a2390298.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论