mysql - 在 HasOne 关系中获取关联错误

Question

我的表
下面是将 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 无关!”

我不知道我哪里做错了
请标记可以提供帮助的人

Answer 1

我认为 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