mysql - 如何在相关表中添加子行？（续集 N:M 关系）

问题描述

在我的程序中，我想创建一个名为Column的新帖子，其中包含许多类别。此外，一个类别可以包含许多帖子。（N：M）

所以我尝试在帖子中添加一个类别，但失败了。这是我尝试过的代码：但是，没有错误输出到日志。

for(var i in column_categorys) {
    const category = await Category.findOrCreate({
      where : {
        category_name: column_categorys[i],
      }
    })
    .then(resolve => {
      return resolve;
    })
    .catch(function(error) {
      isFail = true;
      return null;
    });

    await column.addCategory(
      category.id,
      { through:
        { selfGranted: false }
      }
    )
    .catch(err => {
      console.log(err);
    });
  }

column_info.js

const Sequelize = require('sequelize');

module.exports = class Column extends Sequelize.Model {
  static init(sequelize) {
    return super.init({
      column_title: {
        type: Sequelize.STRING(200),
        allowNull: false,
        unique: true,
      },
      column_content_text: {
        type: Sequelize.STRING(10000),
        allowNull: false,
      },
      column_content_html: {
        type: Sequelize.STRING(10000),
      },
      column_thumbnail_url: {
        type: Sequelize.STRING(300),
      },
    }, {
      sequelize,
      timestamps: true,
      underscored: true,
      modelName: 'Column',
      tableName: 'columns',
      paranoid: true,
      charset: 'utf8',
      collate: 'utf8_general_ci',
    });
  }

  static associate(db) {
    db.Column.belongsToMany(db.Category, {
      through: 'ColumnCategory',
      unique: false,
    });
    db.Column.belongsToMany(db.Tag, {
      through: 'ColumnTag',
      unique: false,
    });
  }
};

category_info.js

const Sequelize = require('sequelize');

module.exports = class Category extends Sequelize.Model {
  static init(sequelize) {
    return super.init({
      category_name: {
        type: Sequelize.STRING(50),
        allowNull: false,
        unique: true,
      },
    }, {
      sequelize,
      timestamps: false,
      underscored: true,
      modelName: 'Category',
      tableName: 'categorys',
      paranoid: false,
      charset: 'utf8',
      collate: 'utf8_general_ci',
    });
  }

  static associate(db) {
    db.Category.belongsToMany(db.Column, {
      through: 'ColumnCategory',
      unique: false,
    });
  }
};

模型/index.js

const Sequelize = require('sequelize');
const User = require('./user');
const Baby = require('./baby_info');
const UserCase = require('./user_case');
const Column = require('./column_info');
const Tag = require('./tag_info');
const Category = require('./category_info');

const env = process.env.NODE_ENV || 'development';
const config = require(__dirname + '/../config/config')[env];
const db = {};

const sequelize = new Sequelize(config.database, config.username, config.password, config);

db.sequelize = sequelize;

db.User = User;
db.Baby = Baby;
db.UserCase = UserCase;
db.Column = Column;
db.Tag = Tag;
db.Category = Category;

User.init(sequelize);
Baby.init(sequelize);
UserCase.init(sequelize);
Column.init(sequelize);
Tag.init(sequelize);
Category.init(sequelize);

User.associate(db);
Baby.associate(db);
UserCase.associate(db);
Column.associate(db);
Tag.associate(db);
Category.associate(db);

module.exports = db;

你能告诉我这段代码的哪一部分我错误地使用了关系查询吗？

最新版本

let getColumn = async function(req, res) {
  const result = await Column.findOne({
    where: {
      column_title: req.body.column_name,
    },
    include: Category,
  });

  res.send({result: result});
}

let insertColumn = async function(req, res) {
  let isFail = false;

  let column_title = req.body.title;
  let column_content_text = req.body.contentText;
  let column_content_html = req.body.contentHTML;
  let column_categorys = req.body.categoris;
  let column_tags = req.body.tags;

  let column_thumbnail_url = process.env.CDN_ENDPOINT;
  column_thumbnail_url += column_title;
  column_thumbnail_url += '/';
  column_thumbnail_url += '0.png';

  await sequelize.transaction(async transaction => {
    try {
      const column = await Column.create({
        column_title: column_title,
        column_content_text: column_content_text,
        column_content_html: column_content_html,
        column_thumbnail_url: column_thumbnail_url,
      }, {transaction: transaction});

      for(var i in column_categorys) {
        const category = await Category.findOrCreate({
          where : {
            category_name: column_categorys[i],
          },
          transaction,
        });
        await column.addCategory(
          category.id,
          { transaction },
        );
      }

      for(var j in column_tags) {
        const tag = await Tag.findOrCreate({
          where : {
            tag_name: column_tags[j],
          },
          transaction,
        });

        await column.addTag(
          tag.id,
          { transaction },
        );
      }
    } catch (err) {
      console.log(err);
      isFail = true;
    }
  });

  if(isFail) {
    res.json({
      responseCode: 404,
      responseMsg: 'Fail'
    });
  } else {
    res.json({
      responseCode: 400,
      responseMsg: 'Success'
    });
  }
}

responseCode 为 400，没有错误信息。但什么都没有ColumnCategory。

下一个是日志消息。

0|app  | 21-01-03 11:27:28: Executing (9904bd92-1a06-4f58-853f-9517d560ca50): START TRANSACTION;
0|app  | 21-01-03 11:27:28: Executing (9904bd92-1a06-4f58-853f-9517d560ca50): INSERT INTO `columns` (`id`,`column_title`,`column_content_text`,`column_content_html`,`column_thumbnail_url`,`created_at`,`updated_at`) VALUES (DEFAULT,?,?,?,?,?,?);
0|app  | 21-01-03 11:27:28: Executing (9904bd92-1a06-4f58-853f-9517d560ca50): SAVEPOINT `9904bd92-1a06-4f58-853f-9517d560ca50-sp-1`;
0|app  | 21-01-03 11:27:28: Executing (9904bd92-1a06-4f58-853f-9517d560ca50): SELECT `id`, `category_name` FROM `categorys` AS `Category` WHERE `Category`.`category_name` = 'category1';
0|app  | 21-01-03 11:27:28: Executing (9904bd92-1a06-4f58-853f-9517d560ca50): SAVEPOINT `9904bd92-1a06-4f58-853f-9517d560ca50-sp-2`;
0|app  | 21-01-03 11:27:28: Executing (9904bd92-1a06-4f58-853f-9517d560ca50): SELECT `id`, `category_name` FROM `categorys` AS `Category` WHERE `Category`.`category_name` = 'category2';
0|app  | 21-01-03 11:27:28: Executing (9904bd92-1a06-4f58-853f-9517d560ca50): SAVEPOINT `9904bd92-1a06-4f58-853f-9517d560ca50-sp-3`;
0|app  | 21-01-03 11:27:28: Executing (9904bd92-1a06-4f58-853f-9517d560ca50): SELECT `id`, `tag_name` FROM `tags` AS `Tag` WHERE `Tag`.`tag_name` = 'tag1' LIMIT 1;
0|app  | 21-01-03 11:27:28: Executing (9904bd92-1a06-4f58-853f-9517d560ca50): SAVEPOINT `9904bd92-1a06-4f58-853f-9517d560ca50-sp-4`;
0|app  | 21-01-03 11:27:28: Executing (9904bd92-1a06-4f58-853f-9517d560ca50): SELECT `id`, `tag_name` FROM `tags` AS `Tag` WHERE `Tag`.`tag_name` = 'tag2' LIMIT 1;
0|app  | 21-01-03 11:27:28: Executing (9904bd92-1a06-4f58-853f-9517d560ca50): COMMIT;
0|app  | 21-01-03 11:27:28: POST /api/column 200 55.857 ms - 73

标签： mysqlnode.jsexpresssequelize.js

mysql - 如何在相关表中添加子行？（续集 N:M 关系）

问题描述

最新版本

解决方案

推荐阅读