javascript - 从 Sequelize 中的另一个表中计数

Question

我必须将此 SQL 重写为 Sequelize:

SELECT table1.id, table1.name, 
    (SELECT COUNT(*) FROM table2 WHERE table2.t1_id = table1.id) AS t1_count 
FROM table1 WHERE .... LIMIT ..... OFFSET .....;

谢谢 :)

Answer 1

要在您的示例中生成确切的 SQL，请使用 sequelize.literal() 生成子查询。

const result = await Table1.findByPk(id, {
  attributes: [
    'id', 
    'name',
    [
      sequelize.literal(`(
        SELECT COUNT(table2.id)
        FROM table2
        WHERE table2.id = table1.t1_id
      )`),
      't1_count',
    ],
  ],
  where: {
    // ...
  },
  limit: {
    // ...
  },
  offset: {
    // ...
  },
});

您也可以通过使用 LEFT JOIN 然后使用 sequelize.fn() 对连接的结果调用 COUNT() 来完成此操作。

const result = await Table1.findByPk(id, {
  attributes: [
    'id', 
    'name',
    [
      sequelize.fn('COUNT', sequelize.col('table2.id')),
      't1_count',
    ],
  ],
  include: {
    model: Table2,
    attributes: [],
    where: {
      id: {
        [Op.col]: sequelize.col('table1.t1_id'),
      },
    },
    required: false,
  },
  where: {
    // ...
  },
  limit: {
    // ...
  },
  offset: {
    // ...
  },
});

SELECT id, name, COUNT(table2.id)
FROM table1
LEFT JOIN table2 ON table2.id = table1.t1_id 
WHERE .... LIMIT ..... OFFSET .....;