我有两个表Users和Shops，用户可以有多个商店，商店可以由一个用户拥有。
我正在使用Node.js&Sequelize ORM(postgres(
问题
当从Shop模型获取数据时，Sequelize在选择查询中添加额外的字段UserId，如：
SELECT "id", "userId", "createdAt", "updatedAt", "UserId" FROM "Shops" AS "Shop";
当我这样做时，此查询被排除：
const shops = await Shop.findAll();
该行抛出异常表示`error:column"用户ID"；不存在'。
StackTrace:
Error
at Query.run (F:WorkCodingstacknode_modulessequelizelibdialectspostgresquery.js:50:25)
at F:WorkCodingstacknode_modulessequelizelibsequelize.js:313:28
at processTicksAndRejections (node:internal/process/task_queues:96:5)
at async PostgresQueryInterface.select (F:WorkCodingstacknode_modulessequelizelibdialectsabstractquery-interface.js:396:12)
at async Function.findAll (F:WorkCodingstacknode_modulessequelizelibmodel.js:1119:21)
at async F:WorkCodingstackapp.js:47:19 {
name: 'SequelizeDatabaseError',
parent: error: column "UserId" does not exist
at Parser.parseErrorMessage (F:WorkCodingstacknode_modulespg-protocoldistparser.js:287:98)
at Parser.handlePacket (F:WorkCodingstacknode_modulespg-protocoldistparser.js:126:29)
at Parser.parse (F:WorkCodingstacknode_modulespg-protocoldistparser.js:39:38)
at Socket.<anonymous> (F:WorkCodingstacknode_modulespg-protocoldistindex.js:11:42)
at Socket.emit (node:events:390:28)
at addChunk (node:internal/streams/readable:315:12)
at readableAddChunk (node:internal/streams/readable:289:9)
at Socket.Readable.push (node:internal/streams/readable:228:10)
at TCP.onStreamRead (node:internal/stream_base_commons:199:23) {
length: 164,
severity: 'ERROR',
code: '42703',
detail: undefined,
hint: 'Perhaps you meant to reference the column "Shop.userId".',
position: '50',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'parse_relation.c',
line: '3599',
routine: 'errorMissingColumn',
sql: 'SELECT "id", "userId", "createdAt", "updatedAt", "UserId" FROM "Shops" AS "Shop";',
parameters: undefined
},
original: error: column "UserId" does not exist
at Parser.parseErrorMessage (F:WorkCodingstacknode_modulespg-protocoldistparser.js:287:98)
at Parser.handlePacket (F:WorkCodingstacknode_modulespg-protocoldistparser.js:126:29)
at Parser.parse (F:WorkCodingstacknode_modulespg-protocoldistparser.js:39:38)
at Socket.<anonymous> (F:WorkCodingstacknode_modulespg-protocoldistindex.js:11:42)
at Socket.emit (node:events:390:28)
at addChunk (node:internal/streams/readable:315:12)
at readableAddChunk (node:internal/streams/readable:289:9)
at Socket.Readable.push (node:internal/streams/readable:228:10)
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'parse_relation.c',
line: '3599',
routine: 'errorMissingColumn',
sql: 'SELECT "id", "userId", "createdAt", "updatedAt", "UserId" FROM "Shops" AS "Shop";',
parameters: undefined
},
sql: 'SELECT "id", "userId", "createdAt", "updatedAt", "UserId" FROM "Shops" AS "Shop";',
parameters: {}
}
JavaScript代码
const { Sequelize, Model, DataTypes } = require('sequelize');
const connectToDB = async () => {
const sequelize = new Sequelize("postgres://postgres:root@localhost:5432/testDB", {
dialect: 'postgres'
});
await sequelize.authenticate();
console.log('Database connected...');
return sequelize;
}
connectToDB().then(async (sequelize) => {
// --- Craete User Model ---
class User extends Model { }
await User.init({
id: {
primaryKey: true,
type: DataTypes.UUID,
defaultValue: Sequelize.UUIDV4,
}
}, { sequelize, modelName: 'User' })
await User.sync();
// --- Craete Shop Model ---
class Shop extends Model { }
await Shop.init({
id: {
primaryKey: true,
type: DataTypes.UUID,
defaultValue: Sequelize.UUIDV4,
},
userId: {
type: DataTypes.UUID,
allowNull: false
}
}, { sequelize, modelName: 'Shop' })
await Shop.sync();
/* RELATION BETWEEN User AND Shop (one-to-many) */
await User.hasMany(Shop, {
foreignKey: 'userId',
onDelete: "CASCADE",
})
await Shop.belongsTo(User)
// --- Find All Shops --
const shops = await Shop.findAll();
console.log(shops);
}).catch((err) => console.error(err));
表格的创建可以在控制台中打印：
Executing (default): SELECT 1+1 AS result
Executing (default): CREATE TABLE IF NOT EXISTS "Users" ("id" UUID , "createdAt"TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'Users' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): CREATE TABLE IF NOT EXISTS "Shops" ("id" UUID , "userId" UUID NOT NULL, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'Shops' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
您需要在两个关系中指示相同的foreignKey选项值：
/* RELATION BETWEEN User AND Shop (one-to-many) */
User.hasMany(Shop, {
foreignKey: 'userId',
onDelete: "CASCADE",
})
Shop.belongsTo(User, {
foreignKey: 'userId',
})
附言，不需要指示CCD_ 6，因为这些是同步功能。