node.js - 这个 SQL 是原始 sql 选项的东西还是 Sequelize 有它的东西？

Question

这个 SQL 是原始 sql 选项的东西还是 Sequelize 有它的东西？

select 
sum(case when field1 = 3 then 1 else 0 end) as status1
,sum(case when field1 = 5 then 1 else 0 end) as status2
from my_table

Answer 1

您可以按如下方式进行“半原始”操作:

Foo.findAll({
    attributes: [
        [Sequelize.literal("sum(case when field1 = 3 then 1 else 0 end)"), "status1"],
        [Sequelize.literal("sum(case when field1 = 5 then 1 else 0 end)"), "status2"]
    ]
})

测试行为的完整代码:

const Sequelize = require('sequelize');
const sequelize = new Sequelize({ dialect: 'sqlite', storage: 'db.sqlite' });
const Foo = sequelize.define("foo", {
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        allowNull: false
    },
    field1: Sequelize.INTEGER
});
sequelize.sync().then(() => Promise.all([
    Foo.create({ field1: 3 }),
    Foo.create({ field1: 4 }),
    Foo.create({ field1: 5 }),
    Foo.create({ field1: 5 }),
    Foo.create({ field1: 3 }),
    Foo.create({ field1: 5 })
])).then(() => Foo.findAll({
    attributes: [
        [Sequelize.literal("sum(case when field1 = 3 then 1 else 0 end)"), "status1"],
        [Sequelize.literal("sum(case when field1 = 5 then 1 else 0 end)"), "status2"]
    ]
})).then(result => {
    console.log(result[0].toJSON());
});

结果是:

{ status1: 2, status2: 3 }