Hello, Sequelize

Hello, Sequelize!

See https://sequelize.org/.

主表多个字段与从表一对多关联

一张表的多个字段与另外一张表做一对多关联。

• Order.js

static associate() {
  this.belongsTo(Party, { foreignKey: 'party1', as: 'p1' });
  this.belongsTo(Party, { foreignKey: 'party2', as: 'p2' });
}

• Party.js

static associate() {
  //this.hasMany(Order, { as: 'party1' });
  //this.hasMany(Order, { as: 'party2' });
}

• Controller.js

async list(ctx) {
  let code = Controller.CODE.OK;
  let message = 'OK';
  let where = ctx.query;
  let attributes = ['id', 'code', 'amount', 'currency', 'currencyRate'];
  let include = [
    {
      model: Party,
      alias: 'party1',
      as: 'p1',
      attributes: ['name'],
    },
    {
      model: Party,
      alias: 'party2',
      as: 'p2',
      attributes: ['name'],
    },
  ];
  let order = [['id', 'desc']];
  let orders = await Order.findAll({ where, attributes, include, order });
    ctx.body = { code, message, orders };
  }

• SQL

SELECT `Order`.`id`, `Order`.`code`, `Order`.`amount`, `Order`.`currency`, `Order`.`currencyRate`
	, `p1`.`id` AS `p1.id`, `p1`.`name` AS `p1.name`, `p2`.`id` AS `p2.id`, `p2`.`name` AS `p2.name`
FROM `Orders` `Order`
	LEFT JOIN `Parties` `p1` ON `Order`.`party1` = `p1`.`id`
	LEFT JOIN `Parties` `p2` ON `Order`.`party2` = `p2`.`id`
WHERE `Order`.`type` = '1'
ORDER BY `Order`.`id` DESC;

关联表查询条件

async sum2(ctx) {
  let code = Controller.CODE.OK;
  let message = 'OK';
  let year = ctx.request.query.year;
  if (!year) {
    year = new Date().getFullYear();
  } else {
    year = Number(year);
  }
  let attributes = [
    [sequelize.fn('SUM', sequelize.col('purchasePrice')), 'amount1'],
    [sequelize.fn('SUM', sequelize.col('salesPrice')), 'amount2'],
  ];
  let from = new Date(Date.UTC(year, 0, 1));
  let to = new Date(Date.UTC(year + 1, 0, 1));
  let include = [
    {
      model: Order,
      alias: 'salesOrderID',
      as: 'so',
      attributes: [],
      where: {
        signedOn: {
          [Sequelize.Op.between]: [from, to],
        },
      },
    },
  ];
  let where = {};
  let sums = await Item.findOne({ attributes, where, include });
  ctx.body = { code, message, sums };
}

SELECT `Item`.`id`, SUM(`purchasePrice`) AS `amount1`, SUM(`salesPrice`) AS `amount2` 
FROM `Items` AS `Item` 
INNER JOIN `Orders` AS `so` ON `Item`.`salesOrderID` = `so`.`id` 
AND `so`.`signedOn` BETWEEN '2021-01-01' AND '2022-01-01' LIMIT 1;

使用SQL函数操作字段并作为列

对查询字段进行函数操作，使用sequelize.literal。注意查询条件里的用法。

async sumAmountGroupByMonths(ctx) {
  let code = Controller.CODE.OK;
  let message = 'OK';
  let year = ctx.params.year;
  let attributes = [
    [sequelize.fn('SUM', sequelize.col('amountCr')), 'cr'],
    [sequelize.fn('SUM', sequelize.col('amountDr')), 'dr'],
    [sequelize.literal('MONTH(`transactionDate`)'), 'month'],
  ];
  let where = {
    year: sequelize.literal('YEAR(`transactionDate`)=' + year),
  };
  let group = ['month'];
  let sums = await Payment.findOne({ attributes, where, group });
  ctx.body = { code, message, sums };
}

输出SQL如下：

SELECT
  SUM(`amountCr`) AS `cr`,
  SUM(`amountDr`) AS `dr`, 
  MONTH(`transactionDate`) AS `month` 
FROM `Payments` AS `Payment` 
WHERE YEAR(`transactionDate`)=2021 
GROUP BY `month` LIMIT 1;

Troubleshooting

Foreign key constraint is incorrectly formed @ 2021-10-21

SequelizeDatabaseError: (conn=1934, no: 1005, SQLState: HY000) Can’t create table *.* (errno: 150 “Foreign key constraint is incorrectly formed”)
A表外键我设定的数据类型是DataTypes.BIGINT，B表的主键是系统默认生成的自增字段ID。可能是数据类型不一致导致，将外键改为DataTypes.INTEGER就OK了。