node.js - 在 Sequelize.js 的聚合函数中使用 case-when

Question

是否可以在模型上构建以下查询而不是对原始 sql 使用 sequilize.query()

select 
  queue,
  count(*) total,
  sum(case when type = 'Proactive' then 1 else 0 end) proactive,
  sum(case when type = 'Reactive' then 1 else 0 end) reactive
from "OpenIncidents" 
group by queue
order by total DESC;

我厌倦了将 sequelize.fn() 用于聚合函数，但在文档中找不到任何关于聚合函数支持 CASE WHEN 的信息。

我目前正在使用原始查询

return sequelize.query(
    `
    SELECT 
      queue, 
      COUNT(*) total, 
      SUM(CASE WHEN type = 'Proactive' THEN 1 ELSE 0 END) proactive, 
      SUM(CASE WHEN type = 'Reactive' THEN 1 ELSE 0 END) reactive 
    FROM "OpenIncidents" 
    GROUP BY queue 
    ORDER BY total DESC;
    `, 
    { type: sequelize.QueryTypes.SELECT })
  .then(queues => res.json(queues))
  .catch(err => res.status(400).json(err));

有没有办法直接在 Sequelize 模型上做，如下所示

return OpenIncident.findAll({
  attributes:[
    'queue',
    [sequelize.fn('COUNT', sequelize.col('id')), 'total'],
    [sequelize.fn('SUM', CASE_WHEN_STATEMENT, 'proactive'],
    [sequelize.fn('SUM', CASE_WHEN_STATEMENT), 'reactive']
  ],
  group: ['queue'],
  order: [sequelize.fn('COUNT', sequelize.col('id')), 'DESC']
})

Answer 1

一种解决方案是使用“Sequelize.literal()”，如下所示:

[Sequelize.fn('SUM', Sequelize.literal('CASE WHEN type = 'Proactive' THEN 1 ELSE 0 END')), 'proactive']