mysql-了解Sequelize中的关联

我正在尝试了解Sequelize中的关联.我从现有的数据库表开始,因此某些字段可能与Sequelize中的默认值不匹配.我使用Sequelizer直接从数据库生成模型.
我习惯于编写查询,但是现在我试图学习像Sequelize这样的ORM的工作方式.

这是我的模特.

型号/user.js

module.exports = (sequelize,DataTypes) => {
  const User = sequelize.define(
    "User",{
      id: {
        type: DataTypes.INTEGER(11),allowNull: false,primaryKey: true,field: "id"
      },username: {
        type: DataTypes.STRING(20),field: "username"
      },fullname: {
        type: DataTypes.STRING(60),field: "fullname"
      },createdat: {
        type: DataTypes.DATE,field: "createdat"
      },updateat: {
        type: DataTypes.DATE,allowNull: true,field: "updateat"
      },deletedat: {
        type: DataTypes.DATE,field: "deletedat"
      }
    },{
      tableName: "users",timestamps: false
    }
  );

  User.associate = function(models) {
        models.User.hasMany(models.Ticket),{ as: "createdbyname",foreignKey: "createdby" };
  };
  return User;
};

models / ticket.js

module.exports = (sequelize,DataTypes) => {
  const Ticket = sequelize.define(
    "Ticket",details: {
        type: DataTypes.STRING(45),field: "details"
      },assignedto: {
        type: DataTypes.INTEGER(11),field: "assignedto"
      },createdby: {
        type: DataTypes.INTEGER(11),field: "createdby"
      },updatedat: {
        type: DataTypes.DATE,field: "updatedat"
      },{
      tableName: "tickets",timestamps: false
    }
  );

  Ticket.associate = function(models) {
        models.Ticket.belongsTo(models.User,{ foreignKey: "createdby" });
  };
  return Ticket;
};

在我的路由处理程序中,我按如下方式调用User.findAll：

  models.User.findAll({
    include: [models.Ticket]
  })

我希望看到的结果是一个查询,如下所示：

SELECT 
    `User`.`id`,`User`.`username`,`User`.`fullname`,`User`.`createdat`,`User`.`updateat`,`User`.`deletedat`,`Tickets`.`id` AS `Tickets.id`,`Tickets`.`details` AS `Tickets.details`,`Tickets`.`assignedto` AS `Tickets.assignedto`,`Tickets`.`createdby` AS `Tickets.createdby`,`Tickets`.`createdat` AS `Tickets.createdat`,`Tickets`.`updatedat` AS `Tickets.updatedat`,`Tickets`.`deletedat` AS `Tickets.deletedat`
FROM
    `users` AS `User`
LEFT OUTER JOIN
    `tickets` AS `Tickets` ON `User`.`id` = `Tickets`.`createdby`

我看到在控制台中运行的查询是：

SELECT 
    `User`.`id`,`Tickets`.`deletedat` AS `Tickets.deletedat`,`Tickets`.`UserId` AS `Tickets.UserId`
FROM
    `users` AS `User`
LEFT OUTER JOIN
    `tickets` AS `Tickets` ON `User`.`id` = `Tickets`.`UserId`;

注意LEFT OUTER JOIN子句的区别.这将引发以下错误：

Unhandled rejection SequelizeDatabaseError: Unknown column 'Tickets.UserId' in 'field list'

我需要一些帮助来找出我在哪里出了问题.

最佳答案

在定义关联时,例如belongsTo,可以指定一个foreignKey和一个targetKey. foreignKey对应于源表中的字段(请记住,语法为sourceModel.belongsTo(targetModel,options)). targetKey对应于目标表中的字段.

就您而言,您在models / ticket.js文件中的关联中犯了一个错误,您使用了：

models.Ticket.belongsTo(models.User,{ foreignKey: "createdby" });

在这里,foreignKey引用了源表Ticket.因此,您要告诉Sequelize在Ticket表中使用createdBy字段,在User表中使用默认字段(主键).由于Ticket中不存在createdBy,因此Sequelize会使用默认的情况,即使用Ticket.UserID.

要修复关联(和查询),您需要将自己的belongsTo更新为以下内容：

models.Ticket.belongsTo(models.User,{ targetKey: "createdby" });

原文链接：https://www.f2er.com/mysql/531947.html

mysql-了解Sequelize中的关联

猜你在找的MySQL相关文章