mysql - 在 HasOne 关系中出现关联错误
问题描述
我
下面的表格是分类模板模型,它具有ad_type_id作为外键并与 ad_type 相关联
'use strict';
module.exports = (sequelize, DataTypes) => {
let classified_template = sequelize.define('classified_template', {
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true
},
temp_body: DataTypes.STRING,
active: {
type: DataTypes.BOOLEAN,
defaultValue: true
},
is_deleted: {
type: DataTypes.BOOLEAN,
defaultValue: false
},
ad_type_id:DataTypes.INTEGER,
}, {
tableName: 'classified_template'
});
classified_template.associate = (models) => {
classified_template.hasOne(models.ad_type, {
foreignKey: 'id',
sourceKey: 'ad_type_id',
});
};
return classified_template
};
以下是我的广告类型模型
'use strict';
module.exports = (sequelize, DataTypes) => {
let ad_type = sequelize.define('ad_type', {
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true
},
name: DataTypes.STRING,
active: {
type: DataTypes.BOOLEAN,
defaultValue: true
},
is_deleted: {
type: DataTypes.BOOLEAN,
defaultValue: false
},
}, {
tableName: 'ad_type'
});
return ad_type
};
我的关联查询
const result = await adTypeModel.findAndCountAll({
where: {
is_deleted: false
},
include: {
model: classifiedTemplateModel,// included the model
where: {
is_deleted: false
},
},
});
错误:“消息”:“classified_template 未关联到 ad_type!”
我不知道我在哪里做错了
请标记可以提供帮助的人
解决方案
我认为和之间的关联ad_type
是classified_template
一对一关联。您还需要添加以下关联:
AdType.belongsTo(ClassifiedTemplate, { foreignKey: 'id', targetKey: 'ad_type_id' });
这是一个工作示例:
import { sequelize } from '../../db';
import { Model, DataTypes } from 'sequelize';
class ClassifiedTemplate extends Model {}
ClassifiedTemplate.init(
{
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true,
},
temp_body: DataTypes.STRING,
active: {
type: DataTypes.BOOLEAN,
defaultValue: true,
},
is_deleted: {
type: DataTypes.BOOLEAN,
defaultValue: false,
},
ad_type_id: {
type: DataTypes.INTEGER,
unique: true,
},
},
{ sequelize, modelName: 'classified_template', tableName: 'classified_template' },
);
class AdType extends Model {}
AdType.init(
{
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true,
},
name: DataTypes.STRING,
active: {
type: DataTypes.BOOLEAN,
defaultValue: true,
},
is_deleted: {
type: DataTypes.BOOLEAN,
defaultValue: false,
},
},
{ sequelize, modelName: 'ad_type', tableName: 'ad_type' },
);
ClassifiedTemplate.hasOne(AdType, { foreignKey: 'id', sourceKey: 'ad_type_id' });
AdType.belongsTo(ClassifiedTemplate, { foreignKey: 'id', targetKey: 'ad_type_id' });
(async function test() {
try {
// create tables
await sequelize.sync({ force: true });
// seed
await ClassifiedTemplate.create(
{
temp_body: 'temp_body',
ad_type_id: 1,
ad_type: {
name: 'ad type name',
},
},
{ include: [AdType] },
);
// test
const result = await AdType.findAndCountAll({
where: {
is_deleted: false,
},
include: [
{
model: ClassifiedTemplate,
where: {
is_deleted: false,
},
},
],
});
console.log('result:', result);
} catch (error) {
console.log(error);
} finally {
await sequelize.close();
}
})();
执行结果:
Executing (default): DROP TABLE IF EXISTS "ad_type" CASCADE;
Executing (default): DROP TABLE IF EXISTS "classified_template" CASCADE;
Executing (default): DROP TABLE IF EXISTS "classified_template" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "classified_template" ("id" SERIAL , "temp_body" VARCHAR(255), "active" BOOLEAN DEFAULT true, "is_deleted" BOOLEAN DEFAULT false, "ad_type_id" INTEGER UNIQUE, 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 = 'classified_template' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): DROP TABLE IF EXISTS "ad_type" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "ad_type" ("id" SERIAL REFERENCES "classified_template" ("ad_type_id") ON DELETE CASCADE ON UPDATE CASCADE, "name" VARCHAR(255), "active" BOOLEAN DEFAULT true, "is_deleted" BOOLEAN DEFAULT false, 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 = 'ad_type' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "classified_template" ("id","temp_body","active","is_deleted","ad_type_id") VALUES (DEFAULT,$1,$2,$3,$4) RETURNING *;
Executing (default): INSERT INTO "ad_type" ("id","name","active","is_deleted") VALUES ($1,$2,$3,$4) RETURNING *;
Executing (default): SELECT count("ad_type"."id") AS "count" FROM "ad_type" AS "ad_type" INNER JOIN "classified_template" AS "classified_template" ON "ad_type"."id" = "classified_template"."ad_type_id" AND "classified_template"."is_deleted" = false WHERE "ad_type"."is_deleted" = false;
Executing (default): SELECT "ad_type"."id", "ad_type"."name", "ad_type"."active", "ad_type"."is_deleted", "classified_template"."id" AS "classified_template.id", "classified_template"."temp_body" AS "classified_template.temp_body", "classified_template"."active" AS "classified_template.active", "classified_template"."is_deleted" AS "classified_template.is_deleted", "classified_template"."ad_type_id" AS "classified_template.ad_type_id" FROM "ad_type" AS "ad_type" INNER JOIN "classified_template" AS "classified_template" ON "ad_type"."id" = "classified_template"."ad_type_id" AND "classified_template"."is_deleted" = false WHERE "ad_type"."is_deleted" = false;
result: { count: 1,
rows:
[ ad_type {
dataValues: [Object],
_previousDataValues: [Object],
_changed: {},
_modelOptions: [Object],
_options: [Object],
isNewRecord: false,
classified_template: [classified_template] } ] }
检查数据库:
node-sequelize-examples=# select * from "classified_template";
id | temp_body | active | is_deleted | ad_type_id
----+-----------+--------+------------+------------
1 | temp_body | t | f | 1
(1 row)
node-sequelize-examples=# select * from "ad_type";
id | name | active | is_deleted
----+--------------+--------+------------
1 | ad type name | t | f
(1 row)
依赖版本:"sequelize": "^5.21.3"
.
源代码:https ://github.com/mrdulin/node-sequelize-examples/tree/master/src/examples/stackoverflow/60525029
推荐阅读
- python-3.x - 多任务 CNN 模型提前停止验证损失
- html - Css 计算标记和伪元素之前的输出差异
- javafx - 如何使用 JavaFX 中的循环在 ImageView 中设置图像?
- python - 为什么python中的变量“if __name__ ==”__main__”是全局变量?
- json - fromJson: 输入'IntetnalLinkedHashMap
' 不是 'Recording' 的子类型 - javascript - 如何在 React Native 中居中表单元素
- javascript - 为什么使用 3D 安全信用卡时未激活 Stripe 订阅?
- java - 使用 Gluon 客户端插件构建 APK:RuntimeException:初始化 QuantumRenderer 时出错:找不到合适的管道
- node.js - 为什么我无法连接到我的数据库?
- firebase - 我有这段代码,但似乎有问题